count if different

[PBROW]

Hi,

Need to make a quick countif statement.

I have a column of different countries and need to count the different ones.

For example, say column j contains:

Country of Risk
BRL
USD
USD
EUR
CAD
CAD
ZAR
BEF
GBP
GBP

i want to count the amount of "different" countries without counting the reoccurring ones twice. So in this example it would return 7 countries rather than 10. An extra bonus would be able to exclude EUR in the same formula since "EURO" is not a country but a currency. Probably a piece of cake to most of you but I cant quite get it.

Thanks in advance!

[daddylonglegs]

Aren't they all currencies?

To count different items

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

or to exclude EUR

=SUMPRODUCT(((A1:A10<>"")*(A1:A10<>"EUR"))/COUNTIF(A1:A10,A1:A10&""))

[PBROW]

wow, i came back because i forgot to include a detail and you already answered! you guys are so quick. This works perfectly but I need to add one more "IF". The country codes are in column J but i also need to filter out column by account number.

So if A="account#" then count the number of unique country codes. Im gonna play around with it for a few. Thanks for your help.