SumProduct

[ram]

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(tblProcessorActivity!C:C)))*(tblProcessorActivity!N1:N30000=4),tblProcessorActivity!D130000)

 

[Dave Peterson]

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]

Yes it was the column headings.

Thanks for the help David

 

[Bernd P]

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