CODEWITHSUNDEEP
excelformula
Dixon Leong
Dixon Leong

Reputation: 21

Distinct/Unique count with criteria

Source File Calculation sheet

Hi, I'm trying to calculate the unique counts of column C of my source file Product (Import ID) with criteria it matches column B - Manufacturer (Import ID). Googled around and used below formula but returned no results. Anyone could help me where went wrong ??

=SUM(IF(FREQUENCY('Report 1'!C:C,'Report 1'!C:C)>0,('Report 1'!B:B='mapped stat'!B55)*1,0))

Where Report1 column C is the unique count range I'm trying to count & Report1 column B is the condition/criteria I'm trying to match with my calculation sheet (mappedstat)

Upvotes: 1

Views: 1687

Answers (3)

Dixon Leong
Dixon Leong

Reputation: 21

the formula is working now (exception to a record with div error): =SUMPRODUCT(('Report 1'!$B$2:$B$10829=B55)/(COUNTIFS('Report 1'!$C$2:$C$10829,'Report 1'!$C$2:$C$10829&"",'Report 1'!$B$2:$B$10829, B55)+('Report 1'!$B$2:$B$10829<>B55)))

Would there be another less resource hungry formula ? My data set has around 10k rows x 40 columns, now each time an edit on data source is made, excel will hang as for about 2-3 mins for calculating the formulas.

I googled around the use of frequency and match , and come up with below formula, not sure where went wrong, it doesn't return a value

=SUM(--(FREQUENCY(IF('Report 1'!$C$2:$C$10829<>"",IF('Report 1'!$B$2:$B$10829=B55,MATCH('Report 1'!$C$2:$C$10829,'Report 1'!$C$2:$C$10829,0))),ROW('Report 1'!$C$2:$C$10829)-ROW('Report 1'!C2)+1)>0))

If possible, would like to retain / modify as little as possible for the mappedstat worksheet, as this is linked to a powerbi dashboard

Upvotes: 1

jeffreyweir
jeffreyweir

Reputation: 4834

My go-to formula for calculating all things unique, distinct, or duplicate isn't a formula at all...it's a PivotTable.

Whip up a PivotTable, put Manufacturer in the ROWS pane, put Product in the Values area and change the aggregation to COUNT. You will get counts of Product by Manufacturer, and from that you can easily apply a Values filter to look for duplicates (where count will be > 1) or unique (where count = 1) values.

Upvotes: 0

user4039065
user4039065

Reputation:

There is a standard COUNTUNIQUE method using SUMPRODUCT and COUNTIF functions; (e.g. SUMPRODUCT(1/COUNTIF(A2:A12, A2:A12&"")) ). This can be expanded to include conditions by changing to a COUNTIFS function but care must be made to ensure that no #DIV/0! error can occur.

=SUMPRODUCT(('R1'!B1:B12=B55)/(COUNTIFS('R1'!C1:C12,'R1'!C1:C12&"",'R1'!B1:B12, B55)+('R1'!B1:B12<>B55)))

Avoid full column range references.

Upvotes: 3

Related Questions