|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 25th, 2010, 03:46 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Formulas
I have two worksheets. I want to feed data from one to the next to tally
occurrences of key words in two columns according to month in another column Worksheet 1 A B C 01/15/2009 Keyword Keyword 01/05/2009 Keyword Keyword 02/12/2009 Keyword Keyword Worksheet 2 A B C January Count KeyWord Worksheet 1 Count Keyword Worksheet 1 February Count Keyword Worksheet 1 Count Keyword Worksheet 1 How can I have the entries from worksheet one auto-count on worksheet 2 for stat totals? 
|
|
#2
May 25th, 2010, 04:52 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif Formulas
Copy and paste the below formula in B1 cell of Sheet2
=IF(A1="","",SUMPRODUCT((MONTH(Sheet1!$A$1:$A$100) =MONTH(VALUE(A1&1)))*(Sheet1!$B$1:$B$100="KEYWORD" ))) Copy and paste the below formula in C1 cell of Sheet2 =IF(A1="","",SUMPRODUCT((MONTH(Sheet1!$A$1:$A$100) =MONTH(VALUE(A1&1)))*(Sheet1!$C$1:$C$100="KEYWORD" ))) Drag the above formula’s for the remaining cells of B & C Columns. Change the cell reference $A$1:$A$100 to your desired range, if required. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "jay" wrote: I have two worksheets. I want to feed data from one to the next to tally occurrences of key words in two columns according to month in another column Worksheet 1 A B C 01/15/2009 Keyword Keyword 01/05/2009 Keyword Keyword 02/12/2009 Keyword Keyword Worksheet 2 A B C January Count KeyWord Worksheet 1 Count Keyword Worksheet 1 February Count Keyword Worksheet 1 Count Keyword Worksheet 1 How can I have the entries from worksheet one auto-count on worksheet 2 for stat totals?
|
|
#3
May 27th, 2010, 10:12 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif Formulas
Let's dumb this down for me.
 Worksheet 1 A2:A750 = dates for the month If I wanted only the January dates, how would that look in your response? Different keywords fall in column B Worksheet 2 A would be total of those that occur in January and B would be total keywords for January January provide total keywords from column B The month criteria gets me. Sorry I'm being "thick" here. "Ms-Exl-Learner" wrote: Copy and paste the below formula in B1 cell of Sheet2 =IF(A1="","",SUMPRODUCT((MONTH(Sheet1!$A$1:$A$100) =MONTH(VALUE(A1&1)))*(Sheet1!$B$1:$B$100="KEYWORD" ))) Copy and paste the below formula in C1 cell of Sheet2 =IF(A1="","",SUMPRODUCT((MONTH(Sheet1!$A$1:$A$100) =MONTH(VALUE(A1&1)))*(Sheet1!$C$1:$C$100="KEYWORD" ))) Drag the above formula’s for the remaining cells of B & C Columns. Change the cell reference $A$1:$A$100 to your desired range, if required. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "jay" wrote: I have two worksheets. I want to feed data from one to the next to tally occurrences of key words in two columns according to month in another column Worksheet 1 A B C 01/15/2009 Keyword Keyword 01/05/2009 Keyword Keyword 02/12/2009 Keyword Keyword Worksheet 2 A B C January Count KeyWord Worksheet 1 Count Keyword Worksheet 1 February Count Keyword Worksheet 1 Count Keyword Worksheet 1 How can I have the entries from worksheet one auto-count on worksheet 2 for stat totals?
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
