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

henryg

Board Regular
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.

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
What version of Excel are you using?
Please update your account details to show this, as it affects what functions you have.

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

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.

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

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<>"")))))

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))

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
=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

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.

Glad we could help & thanks for the feedback.

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.

Replies
2
Views
146
Replies
8
Views
452
Replies
4
Views
200
Replies
6
Views
597
Replies
6
Views
189

1,214,275
Messages
6,118,623
Members
448,844
Latest member
Jayee04e

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back