Conditional Unique Record Count

Russel

New Member
Joined
Mar 24, 2004
Messages
1
The following array formula counts the number of unique
values found in the range A1:A10 (excluding blank cells
and text enries):
SUM(IF(FREQUENCY(A1:A10,A1:A10)>0,1))
Is it possible to add an additional condition to this
formula in order to return the unique records for the
specified condition only?
An example to further clarify:
Job.No Colour
1055 Red
1066 Blue
1055 Red
1077 Red
1088 Green
The answer required is 2, as there are 2 unique Job.No's
for the Colour "red". Is it possible to type a formula into one cell that will provide this 'Conditional' unique record count?
Your attention to this problem is greatly appreciated.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Welcome!

I was sure it could be done shorter, and maybe with sumproduct. But after trying and trying I give up and give you this formula that has to be entered with Ctrl + Shift + Enter.

=COUNTIF(B1:B10,"RED")-SUM((A1:A10&B1:B10=TRANSPOSE(A1:A10&B1:B10))*(B1:B10="RED"))/2+COUNTIF(B1:B10,"RED")/2
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
With the morefunc.xll add-in installed and D1 housing the condition Red:

=COUNTDIFF(IF((B2:B10=D1)*(A2:A10<>""),A2:A10))-1

Otherwise:

=SUM(IF(FREQUENCY(IF((B1:B10=D1)*(A1:A10<>""),MATCH(A1:A10,A1:A10,0),""),IF((B1:B10=D1)*(A1:A10<>""),MATCH(A1:A10,A1:A10,0),""))>0,1))

Each of these formulas must be confirmed with control+shift+enter instead of just with enter.
 

indiantrix

Well-known Member
Joined
Oct 17, 2002
Messages
943
This may be a simpler solution?
Let us suppose we want to count the unique values in the range A1:A50. We can ARRAY enter the following formula, =Sum(1/Countif(A1:A50,A1:A50)). Hope this helps. /s/ Larry
 

Watch MrExcel Video

Forum statistics

Threads
1,122,915
Messages
5,598,850
Members
414,263
Latest member
sherrcha

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