| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct conditional
Check your original post for answers!
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Carl" wrote in message ... 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) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct conditional
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) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct conditional
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" wrote in message ... 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) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct conditional
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" wrote in message ... 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) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct conditional
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct conditional with dates | Excel Discussion (Misc queries) | |||
| Conditional sumproduct? | New Users to Excel | |||
| Conditional Format Not Working | Excel Discussion (Misc queries) | |||
| Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
| adding two sumproduct formulas together | Excel Worksheet Functions | |||
Linear Mode
