Counting Countries Revisited

[Saloomi]

Following a previous posting and great advice from Aladin Akyurek, I use an array formula to determine the number of DIFFERENT countries occuring in a long list of countries such as below:

China
Denmark
Denmark
Finland

Total = 3

The array formula was:
=SUM(IF(LEN(B2:B5),1/COUNTIF(B2:B5,B2:B5)))
where B2:B5 houses the country data.

Can another condition be added to this array formula? I want it to count the number of countries ONLY if the corresponding value in the adjacent column (say A2:A5) is greater than 0.

 

[eliW]

On 2002-10-13 04:18, Saloomi wrote:
Following a previous posting and great advice from Aladin Akyurek, I use an array formula to determine the number of DIFFERENT countries occuring in a long list of countries such as below:

China
Denmark
Denmark
Finland

Total = 3

The array formula was:
=SUM(IF(LEN(B2:B5),1/COUNTIF(B2:B5,B2:B5)))
where B2:B5 houses the country data.

Can another condition be added to this array formula? I want it to count the number of countries ONLY if the corresponding value in the adjacent column (say A2:A5) is greater than 0.

Hi Saloomi,
This is not clear enough.
Suppose you have Denemark 3 times:
Denmark 0
Denmark 1
Denmark 2
Do you want to count it once? twice?

Eli
This message was edited by eliW on 2002-10-13 04:39

 

[Saloomi]

Let me clarify.
I want to know how many different countries there are in my list and I want to ignore all rows that have a 0 in them.

Therefore:
Denmark 1
Denmark 0
Denmark 4
Finland 0
Iceland 4


Will return a total of 2 (Denmark and Iceland). I hope that's clearer...

 

[Aladin Akyurek]

In C2 enter & copy down:

=IF(LEN(B2),IF(A2,B2,""),"")

This additional column allows you to keep the same array formula, applied this time to the C-range.

=SUM(IF(LEN(C2:C5),1/COUNTIF(C2:C5,C2:C5)))

Another possibility is the following array formula...

=COUNTDIFF(UNIQUEVALUES(IF(A2:A5>0,B2:B5,"")))-1

which requires that you install the morefunc.xll add-in, downloadable from:

http://longre.free.fr/english/index.html

This is a great add-in.

You can also apply COUNTDIFF to C2:C5...

=COUNTDIFF(C2:C5)-(COUNTBLANK(C2:C5)>0)

which is NOT an array formula.

See...

aaCondUniqueCount Saloomi.xls
	A	B	C	D	E
1
2	0	China		2	2
3	2	Denmark	Denmark	2
4	0	Denmark
5	3	Finland	Finland
6
Sheet1