Counting records f(x) two columns
 

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

Counting records f(x) two columns
 

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


"jkl" wrote:

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

Counting records f(x) two columns
 

Hi JKL,

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

HTH
Martin

Counting records f(x) two columns
 

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.


"JMB" wrote:

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


"jkl" wrote:

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

Counting records f(x) two columns
 

Worked great! Thanks!

"JMB" wrote:

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


"jkl" wrote:

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

Counting records f(x) two columns
 

Also worked great! Thanks.

"JMB" wrote:

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.


"JMB" wrote:

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


"jkl" wrote:

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

Counting records f(x) two columns
 

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

"MartinW" wrote:

Hi JKL,

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

HTH
Martin