Count unique numbers based upon specific criteria

Common problems – different language?
Let's talk in English.

Count unique numbers based upon specific criteria

Poslaťod Palo-admin » 26.10.2010, 09:16

Posted by Julie

Hello,
I have a table with two columns that I need to evaluate. One column is Q1 2010, Q2 2010, Q3 2010, Q4 2010
and I need to count how many numbers in the second column are unique per quarter.
For example:

Quarter Client ID
Q1 2010 111
Q2 2010 222
Q2 2010 111
Q1 2010 222
Q1 2010 111
Q3 2010 222
Q3 2010 333
Q4 2010 111

The results should be
Q1 2010: 2 (number of unique clients for Q1 2010)
Q2 2010: 2
Q3 2010: 1
Q4 2010: 1

Any ideas? I can count the unique characters in column B, but how do I limit it to the set criteria in column A and return the count to the results cell?


Assume your data is in B5:C12.

If you need to count unique Clients ID only,
formula below can be used:

=SUM(IF(FREQUENCY(C5:C12,C5:C12)>0,1))

For a conditional unique count, you have to use so called "array formula" (entered with Ctrl+Shift+Enter).
Based on the criteria you state, adjusted formula can be like so:

{=SUM(IF(FREQUENCY(IF(B5:B12="Q1 2010",C5:C12),C5:C12)>0,1))}

See example here:
http://pc-prog.eu/phpBB3/images/xMyKB/HowTo_CountUniquePerQvartal.xls

But for this type of problems I prefer using of Pivot Tables

Palo
Palo-admin
Administrátor stránky
 
Príspevky: 473
Registrovaný: 18.07.2008, 16:43
Udelené poďakovania: 6 krát
Prijaté poďakovania: 63 krát

Späť na Solving MS Office problems in English

Kto je on-line

Užívatelia prezerajúci fórum: Žiadny registrovaný užívateľ nie je prítomný a 1 hosť

cron