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)

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

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
.

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