How to get a count of unique values based on 2 criteria

[henryg]

I have a list of, say, products with duplicate values allowed in Column A and various numeric values in Column B (eg sales volumes or sales values) including zero. I want a count of the unique items in Column A that have a zero in Column B.

I'm convinced there should be a (simple?) solution, but can't seem to find one.

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this, as it affects what functions you have.

 

[henryg]

Using Excel 365 Insider which I have now added to my signature. And actually I want a count of the unique items.

 

[Fluff]

Using Excel 365 Insider which I have now added to my signature.

Can you please also update you account details to show your version, that way it appears on your mini profile, where most people look.

How about
=COUNTA(UNIQUE(FILTER(A2:A1000,(B2:B1000=0)*(A2:A1000<>""))))

 

[Fluff]

If there is a chance that you will never have any 0 values in col B, try
=IF(COUNT(FILTER(ROW(A2:A1000),(B2:B1000=0)*(A2:A1000<>"")))=0,0,COUNTA(UNIQUE(FILTER(A2:A1000,(B2:B1000=0)*(A2:A1000<>"")))))

 

[Aladin Akyurek]

I have a list of, say, products with duplicate values allowed in Column A and various numeric values in Column B (eg sales volumes or sales values) including zero. I want a count of the unique items in Column A that have a zero in Column B.

I'm convinced there should be a (simple?) solution, but can't seem to find one.

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A100<>"",IF(ISNUMBER(B2:B100),IF(B2:B100=0,MATCH(A2:A100,A2:A100,0)))),ROW(A2:A100)-ROW(A2)+1),1))

If empty cells in B must be considered as housing 0's...

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A100<>"",IF(1-ISNUMBER(1/B2:B100),MATCH(A2:A100,A2:A100,0))),ROW(A2:A100)-ROW(A2)+1),1))

 

[henryg]

Can you please also update you account details to show your version, that way it appears on your mini profile, where most people look.

Done

How about
=COUNTA(UNIQUE(FILTER(A2:A1000,(B2:B1000=0)*(A2:A1000<>""))))

Thanks a lot! I'll give this a try. I should have though of Filter(). I tried all sorts of concatenation and string manipulation, but everything failed when I got to the counta(unique()...) stage

 

[henryg]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A100<>"",IF(ISNUMBER(B2:B100),IF(B2:B100=0,MATCH(A2:A100,A2:A100,0)))),ROW(A2:A100)-ROW(A2)+1),1))

If empty cells in B must be considered as housing 0's...
Control+shift+enter, not just enter:
=SUM(IF(FREQUENCY(IF(A2:A100<>"",IF(1-ISNUMBER(1/B2:B100),MATCH(A2:A100,A2:A100,0))),ROW(A2:A100)-ROW(A2)+1),1))

Cool stuff! Old school array formula, and not so simple after all You're not Mike Garvin incognito are you [rhetorical].

I'd like to say I understand it, but I'm just going to hope that I can work on Fluff's 365 solution. My situation is a bit more complex but I have enough to go on now.

Thanks guys, much appreciated.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[henryg]

Fluff's solution, suitably amended/extended, gave me my answer, so thank you very much! And it found an error where I had missed an effective duplication; took me a while to confirm the error manually.

I feel sure Aladin's solution would have worked, but the new engine/functions made it so much easier.

And yes, my "tongue in cheek" reference to Mike Garvin should have been to Mike Girvin.