Counting records f(x) two columns

[Guest]

I'm looking for the right formula. I have two columns of data. Column A
lists countries where activities are taking place, column B categorizes the
country as one of five types. How can I total the number of different
countries that fall under each category of country?

For example, Australia might appear 6 times in Column A but it should not
count as six instances of the country type associated with it. If should
only count as one instance of that country type. Assuming for a moment that
Oman is given the same country category as Australia, it should also count as
one instance of that country type, regardless of how many times Oman appears
in Column A.

TIA . . .

 

[Guest]

If countries are in A1:A7 and type is in B1:B7

=SUMPRODUCT(--(MATCH(A1:A7,A1:A7,0)=ROW(A1:A7)-ROW(A1)+1),--(B1:B7=1))

Where I am looking for type 1. Change ranges and type as needed (type could
be a reference to a cell that has the type you are looking for).

 

[MartinW]

Hi JKL,

This may help you (or not).<g>
Select your data in Col. A and goto Data>Filter>Advanced Filter,
Check 'Copy to another Location'
Check 'Unique Records Only'
Select a destination cell in the Copy to box
And OK out.

HTH
Martin

 

[Guest]

Of course, I assumed that every instance of "Australia" would be the same
type. If it is possible "Australia" could appear as several different types,
then try:

=SUMPRODUCT(--(MATCH(A1:A7&B1:B7,A1:A7&B1:B7,0)=ROW(A1:A7)-ROW(A1)+1),--(B1:B7=D8))

where D8 is the type you are looking for.

 

[Guest]

Worked great! Thanks!

If countries are in A1:A7 and type is in B1:B7

=SUMPRODUCT(--(MATCH(A1:A7,A1:A7,0)=ROW(A1:A7)-ROW(A1)+1),--(B1:B7=1))

Where I am looking for type 1. Change ranges and type as needed (type could
be a reference to a cell that has the type you are looking for).

 

[Guest]

Also worked great! Thanks.

Of course, I assumed that every instance of "Australia" would be the same
type. If it is possible "Australia" could appear as several different types,
then try:

=SUMPRODUCT(--(MATCH(A1:A7&B1:B7,A1:A7&B1:B7,0)=ROW(A1:A7)-ROW(A1)+1),--(B1:B7=D8))

where D8 is the type you are looking for.

 

[Guest]

Very helpful; I used the JMB approach, but this technique will be useful for
other things I'm doing with this sheet. Thanks.