Countif Formulas
in [Functions]
|
Prev: Question About Importing Columns From A Datasheet to A Workshe
Next: Copying Certain Worksheets depending on certain criterion
From: jay on 24 May 2010 22:46 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?
From: Ms-Exl-Learner on 24 May 2010 23:52 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? > > >
From: jay on 27 May 2010 17:12
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? > > > > > > |