Sumproduct conditional

[Guest]

Data looks like this

Name Project Date Hours
Carl Project 1 01/01/2006 8
Carl Project 1 02/01/2006 8
Jim Project 1 01/01/2006 8
Jim Project 1 01/02/2006 8
Carl Project 2 03/01/2006 8

Trying to create report like this

Name Project Jan Feb Mar Apr
Carl Project1 8 8 8 0
Project2 8 8 8 0
Jim Project1 16 0 0 0

I used the following argument but the values returned in the month cells are
always 0 If I remove one of the conditions it returns the number fo hours
for the project by month or name by month. FYI ('All data!A=Name) ("All
data'!B=Project) ("All data'!C=Date) ("All data'!D=hours) The (A=Name and B
= Project on the report worksheet)

=SUMPRODUCT(--(MONTH('All data'!$C$2:$C$9000)=1),--('All
data'!$A$2:$A$9000=$A$2),--('All data'!$B$2:$B$9000=$B2),'All
data'!$D$2:$D$9000)

 

[RagDyer]

Check your original post for answers!

 

[Guest]

hi Carl,

why not to use a Pivot Table? it will give the information easy

hth
regards from Brazil
Marcelo

"Carl" escreveu:

 

[Guest]

I was able to get the orginal question working, the issue here is that I now
have an additional condition I.E. If (dataMonth equal month) and (dataName
= name) and (dataproject = project) then Sum hours

 

[Guest]

The arguement works on the same sheet as the data, but when I move the
function to the new worksheet the answer becomes zero.

=SUMPRODUCT(--(MONTH('All data'!$C$2:$C$9000)=1),--('All
data'!$A$2:$A$9000=$A$2),--('All data'!$B$2:$B$9000=$B2),'All
data'!$D$2:$D$9000)

 

[Guest]

Thanks, but I have never learned pivot tables, and right now I am determined
to make this function work... LOL