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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,936
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

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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
 

Forum statistics

Threads
1,181,683
Messages
5,931,404
Members
436,788
Latest member
Oteez

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