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

henryg

Board Regular
Joined
Oct 23, 2008
Messages
106
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.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,915
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are you using?
Please update your account details to show this, as it affects what functions you have.
 

henryg

Board Regular
Joined
Oct 23, 2008
Messages
106
Office Version
  1. 365
Platform
  1. Windows
Using Excel 365 Insider which I have now added to my signature. And actually I want a count of the unique items.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,915
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,915
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Board Regular
Joined
Oct 23, 2008
Messages
106
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Oct 23, 2008
Messages
106
Office Version
  1. 365
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,915
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

henryg

Board Regular
Joined
Oct 23, 2008
Messages
106
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,460
Members
414,069
Latest member
StudExcel

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
Top