Count and Sum if Date

[Tina Winter]

I realize this question has been answered before for other scenerios, but I
can't seem to get it to work for mine...Please Help!

Here is my table

A B C D E F
1 11/25/07 11/28/07 12/2/07 12/15/07
2 # 4.00 6.00 2.00 9.00
3 $ 200 300 100 450


I need a formula that sums all row 2 cells that have a November 2007 date in
row 1.

Thank you, thank you, thank you to anyone who can help me.

Tina

 

[Don Guillett]

=SUMPRODUCT((MONTH(1:1)=11)*2:2)

 

[Peo Sjoblom]

One way

=SUMPRODUCT((MONTH(A1:Z1)=11)*(YEAR(A1:Z1)=2007)*(A2:Z3))



--


Regards,


Peo Sjoblom

 

[Tina Winter]

I have tried both suggestions and still get #VALUE as a result. Any
suggestions on what I am doing wrong?

 

[Tina Winter]

Nevermind, I figured it out. My source data is from a pivot table and I was
selecting evaluation cells both inside and outside of the pivot table,
causing the error.

Thanks so much for your help and happy holidays!

 

[Pete_UK]

Try this amendment to Peo's formula:

=SUMPRODUCT((MONTH(C1:Z1)=11)*(YEAR(C1:Z1)=2007)*(C2:Z2))

As he was starting in column A, you have some non-numerics in the
range. I've also made it Z2 at the end, as you had asked to add values
from row 2 only.

Hope this helps.

Pete