Sumproduct conditional with dates

[Guest]

Name Project Date Hours
Carl Project2 1/2/2006 8
Carl Project2 1/3/2006 8
Carl Project2 1/4/2006 8
Jim Project2 2/2/2006 8
Jim Project2 1/2/2006 -8
Carl Project1 2/2/2006 8

Given the above, I am trying to get a cell to sum the hours for a given
person in a particular month. This is driving me crazy! This is a large
table of data.

The rsult I would like to see

Name Jan Feb Mar ETC
Carl 24 8 16 ETC
Jim 8 16 24 ETC

Can someone help me with this?

 

[Guest]

FYI, I tried this =SUMPRODUCT(--(MONTH(C2:C9)=1),--(A1:A10="Carl"),D215)
and I get a value error.

 

[Tim C]

Carl,

Try making the size of your arrays match:

=SUMPRODUCT(--(MONTH(C2:C9)=1),--(A2:A9="Carl"),D29)

Tim C

 

[Guest]

That did it! LOL, simple stuff, but man it is a big aggrevation!

 

[RagDyer]

Does this make it more simple?

Say your *original* datalist is on Sheet1, with labels in Row1 and:
Names in Column A
Project in Column B
Dates in Column C, entered as *true XL dates*
Hours in Column D, entered as numbers

Your "Results" list is on another sheet, with labels in Row1 and:
Names in Column A
Jan to Dec in Columns B to M, entered as *text*.

Enter this formula in B2, and copy across to M2,
then copy down as needed:

=SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)*(TEXT(Sheet1!$C$2:$C$100,"mmm")=B$1)*Sheet1!$D$2:$D$100)

 

[Guest]

THANK YOU VERY MUCH

Carl,

Try making the size of your arrays match:

=SUMPRODUCT(--(MONTH(C2:C9)=1),--(A2:A9="Carl"),D29)

Tim C