Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . . . |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . . . |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . . . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . . . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . . . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help a novice with counting number of records | Excel Discussion (Misc queries) | |||
counting records | Excel Worksheet Functions | |||
Counting unique records | Excel Worksheet Functions | |||
Counting records within a month | New Users to Excel | |||
Counting datapoints/records in time interval | Charts and Charting in Excel |