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

henryg

Board Regular
Joined
Oct 23, 2008
Messages
143
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0
Using Excel 365 Insider which I have now added to my signature. And actually I want a count of the unique items.
 
Upvote 0
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<>""))))
 
Upvote 0
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<>"")))))
 
Upvote 0
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))
 
Upvote 0
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
 
Upvote 0
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 :biggrin: 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.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top