Distinct/Unique count with criteria

Question

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)

When you say it returned no results did you get 0 or #REF or something else? Are your IDs numeric or text? — Carol
– Carol, Commented Nov 28, 2017 at 4:33

user4039065 · Accepted Answer · 2017-11-28 05:58:41Z

3

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.

answered Nov 28, 2017 at 5:58

user4039065

Sign up to request clarification or add additional context in comments.

4 Comments

Dixon Leong Over a year ago

Thanks, it works, I have removed the full column range to static range.

Dixon Leong Over a year ago

But what do you meant by no #DIV/0! error ? under what scenario will these occur?

Dixon Leong Over a year ago

I indeed have 1 row record which returns #DIV/0! error, how I go about to resolve this ?

user4039065 Over a year ago

For an explanation on avoiding #DIV/0! errors see stackoverflow.com/questions/50030971/…

Dixon Leong · Accepted Answer · 2017-11-30 03:49:49Z

1

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

answered Nov 30, 2017 at 3:49

Dixon Leong

215 bronze badges

1 Comment

barry houdini Over a year ago

That version with FREQUENCY should be faster than the one using COUNTIFS. Formula looks OK to me but you have to confirm with CTRL+SHIFT+ENTER so that you get curly braces like { and } around the formula - also you won't get #DIV/0 errors

jeffreyweir · Accepted Answer · 2017-11-28 07:08:45Z

0

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.

answered Nov 28, 2017 at 7:08

jeffreyweir

4,8841 gold badge19 silver badges27 bronze badges

Collectives™ on Stack Overflow

Distinct/Unique count with criteria

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related