sumproduct formula returns #name error

[Guest]

Hi. I am trying to get a sumproduct formula that will look at the date in one column and if its the correct month, total values in a second column. I am getting #Name returned with the formula I have. Can someone tell mewhat I did wrong?


=SUMPRODUCT(--(A2:A500=MONTH(3)),D2500)

A D
Date Amount
03/14/04 62.11
04/04/04 101.54


TIA


Todd

 

[Domenic]

Hi,

try,

=SUMPRODUCT(--(MONTH(A2:A500)=3),--(YEAR(A2:A500)=2004),D2500)

Notice I've added the year as a criteria. This will ensure that it
totals the values for the relevant year and ignore any blank cells in
Column A.

Hope this helps!

 

[hgrove]

Toddù wrote . . .

Hi. I am trying to get a sumproduct formula that will look at the
date in one column and if its the correct month, total values in a
second column. I am getting #Name returned with the formula I
have. Can someone tell mewhat I did wrong?

=SUMPRODUCT(--(A2:A500=MONTH(3)),D2500)

...

Given this formula, it's very likely you have a #NAME? error in one o
more cells in either A2:A500 or D2500. What do the formulas

=SUM(A2:A500)

and

=SUM(D2500)

return? Also, I'd guess you want

=SUMPRODUCT(--(MONTH(A2:A500)=3),D2500

 

[Peo Sjoblom]

=SUMPRODUCT(--(MONTH(A2:A500)=3),D2500)



--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

Hi. I am trying to get a sumproduct formula that will look at the date in

one column and if its the correct month, total values in a second column. I
am getting #Name returned with the formula I have. Can someone tell mewhat
I did wrong?