# Counting Countries Revisited

#### Saloomi

##### New Member
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

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...

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

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

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

Replies
5
Views
115
Replies
3
Views
82
Replies
8
Views
352
Replies
6
Views
408
Replies
2
Views
110

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.

### Which adblocker are you using?

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

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