Sumproduct conditional with dates
 

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?

Sumproduct conditional with dates
 

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

"Carl" wrote:

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?

Sumproduct conditional with dates
 

Carl,

Try making the size of your arrays match:

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

Tim C

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

"Carl" wrote:

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?

Sumproduct conditional with dates
 

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

"Tim C" wrote:

Carl,

Try making the size of your arrays match:

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

Tim C

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

"Carl" wrote:

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?

Sumproduct conditional with dates
 

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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Carl" wrote in message
...
That did it! LOL, simple stuff, but man it is a big aggrevation!

"Tim C" wrote:

Carl,

Try making the size of your arrays match:

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

Tim C

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

"Carl" wrote:

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?

Sumproduct conditional with dates
 

THANK YOU VERY MUCH

"Tim C" wrote:

Carl,

Try making the size of your arrays match:

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

Tim C

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

"Carl" wrote:

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?