Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct
Hi All,
Is there a way to look at a date and only use the month in the criteria. I was trying to use the code below Thanks for any help =SUMPRODUCT((tblProcessorActivity!B1:B30000="CG")* (MONTH(tblProcessorActivity!C1:C30000)=MONTH(MAX(t blProcessorActivity!C:C)))*(tblProcessorActivity!N 1:N30000=4),tblProcessorActivity!D1:D30000) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct
That looks like it should work to me.
What happens when you tried it? Did you get a #Value! error? If yes, then look to see if you have some text in C1:C30000 in that tblprocessoractivity worksheet. =month() won't work with text. ram wrote: Hi All, Is there a way to look at a date and only use the month in the criteria. I was trying to use the code below Thanks for any help =SUMPRODUCT((tblProcessorActivity!B1:B30000="CG")* (MONTH(tblProcessorActivity!C1:C30000)=MONTH(MAX(t blProcessorActivity!C:C)))*(tblProcessorActivity!N 1:N30000=4),tblProcessorActivity!D1:D30000) -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct
Yes it was the column headings.
Thanks for the help David "Dave Peterson" wrote: That looks like it should work to me. What happens when you tried it? Did you get a #Value! error? If yes, then look to see if you have some text in C1:C30000 in that tblprocessoractivity worksheet. =month() won't work with text. ram wrote: Hi All, Is there a way to look at a date and only use the month in the criteria. I was trying to use the code below Thanks for any help =SUMPRODUCT((tblProcessorActivity!B1:B30000="CG")* (MONTH(tblProcessorActivity!C1:C30000)=MONTH(MAX(t blProcessorActivity!C:C)))*(tblProcessorActivity!N 1:N30000=4),tblProcessorActivity!D1:D30000) -- Dave Peterson . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct
Hello,
Two suggestions: 1. You might get wrong results if you have more than 1 year old data (MONTH(1/4/2009) = MONTH(1/4/2010)). 2. In addition to worksheet functions you might want to consider a VBA approach or a Pivot table. Please see http://sulprobil.com/html/excel_newsgroups.html for a comparison. Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
SUMPRODUCT | Excel Discussion (Misc queries) | |||
=SUMPRODUCT | Excel Discussion (Misc queries) |