Counting Countries Revisited

Saloomi

New Member
Joined
Apr 8, 2002
Messages
10
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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
 
Upvote 0
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...
 
Upvote 0
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
ABCDE
1
20China 22
32DenmarkDenmark2
40Denmark 
53FinlandFinland
6
Sheet1
 
Upvote 0

Forum statistics

Threads
1,218,575
Messages
6,143,311
Members
450,477
Latest member
teresab543

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