Sumproduct conditional

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)

Check your original post for answers!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Carl" <[email protected]> wrote in message
news:[email protected]...
> 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)
>

hi Carl,

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

hth
regards from Brazil
Marcelo

"Carl" escreveu:

> 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)
>

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

"RagDyer" wrote:

> Check your original post for answers!
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Carl" <[email protected]> wrote in message
> news:[email protected]...
> > 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)
> >
>
>

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)

"RagDyer" wrote:

> Check your original post for answers!
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Carl" <[email protected]> wrote in message
> news:[email protected]...
> > 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)
> >
>
>

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

"Marcelo" wrote:

> hi Carl,
>
> why not to use a Pivot Table? it will give the information easy
>
> hth
> regards from Brazil
> Marcelo
>
> "Carl" escreveu:
>
> > 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)
> >