Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to count occurances in different columns
I have columns of data (each with a different column name at the top: Jan,
Feb, Mar, etc). The columns consist of three or more different words (apple, pear, orange, etc.) and zero values. I am looking for a formula that will count the occurance of the words in each column based on two criteria: Column Name and Word. For example: If I set cell A1 to Jan and cell B1 to Apple, cell C1 returns all the times Apple appears in the Jan column. The number of columns is different from the number of rows in each column. Any help with this is greatly appreciated. Let me know if more information is needed. Thanks. Dan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to count occurances in different columns
Assuming the months in D1:M1
=SUMPRODUCT(--(OFFSET(D1,,MATCH(A1,D1:M1,0)-1,100,1)=B1)) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "More Macro Help Needed" wrote in message ... I have columns of data (each with a different column name at the top: Jan, Feb, Mar, etc). The columns consist of three or more different words (apple, pear, orange, etc.) and zero values. I am looking for a formula that will count the occurance of the words in each column based on two criteria: Column Name and Word. For example: If I set cell A1 to Jan and cell B1 to Apple, cell C1 returns all the times Apple appears in the Jan column. The number of columns is different from the number of rows in each column. Any help with this is greatly appreciated. Let me know if more information is needed. Thanks. Dan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to count occurances in different columns
Worked great!!! Thanks so much..
"Bob Phillips" wrote: Assuming the months in D1:M1 =SUMPRODUCT(--(OFFSET(D1,,MATCH(A1,D1:M1,0)-1,100,1)=B1)) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "More Macro Help Needed" wrote in message ... I have columns of data (each with a different column name at the top: Jan, Feb, Mar, etc). The columns consist of three or more different words (apple, pear, orange, etc.) and zero values. I am looking for a formula that will count the occurance of the words in each column based on two criteria: Column Name and Word. For example: If I set cell A1 to Jan and cell B1 to Apple, cell C1 returns all the times Apple appears in the Jan column. The number of columns is different from the number of rows in each column. Any help with this is greatly appreciated. Let me know if more information is needed. Thanks. Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count matching occurances | Excel Worksheet Functions | |||
Count Occurances if two conditions true | Excel Worksheet Functions | |||
Count occurances in range of cells | Excel Worksheet Functions | |||
If two columns match then count one. How? Tried countif and sum . | Excel Worksheet Functions | |||
count number of cells based on TWO conditions (2 different columns | Excel Worksheet Functions |