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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,934
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,209
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,144,734
Messages
5,725,982
Members
422,652
Latest member
Elnene1

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