| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Summing
I have, for me, a very complicated scenario and I am hoping someone can help.
I need to populate a table with the total number of work hours a person is responsible for on a given day. For example, I need to return the total number of work hours for Jones on 6/3/2008. My data looks like this: Task Name Work Hours Start Date End Date 1 Jones 2 6/2/2008 6/13/2008 2 Smith 1 5/26/2008 6/30/2008 3 Smith 0.5 6/4/2008 6/6/2008 4 Jones 5 5/30/2008 6/12/2008 5 Jones 0.5 6/9/2008 6/18/2008 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Summing
With dates in column G and Jones in H1 and Smith in I1, copy this formula
into H2 and then copy across and down to fill the table. =SUMPRODUCT(($B$2:$B$6=H$1)*($D$2:$D$6<=$G2)*($E$2 :$E$6=$G2)*$C$2:$C$6) If actual data contains more names - add after 'Smith' and continue to copy... Hope this helps! "Brian" wrote: I have, for me, a very complicated scenario and I am hoping someone can help. I need to populate a table with the total number of work hours a person is responsible for on a given day. For example, I need to return the total number of work hours for Jones on 6/3/2008. My data looks like this: Task Name Work Hours Start Date End Date 1 Jones 2 6/2/2008 6/13/2008 2 Smith 1 5/26/2008 6/30/2008 3 Smith 0.5 6/4/2008 6/6/2008 4 Jones 5 5/30/2008 6/12/2008 5 Jones 0.5 6/9/2008 6/18/2008 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Summing
Magic, it works. I really need to learn this formula, thanks!
"BoniM" wrote: With dates in column G and Jones in H1 and Smith in I1, copy this formula into H2 and then copy across and down to fill the table. =SUMPRODUCT(($B$2:$B$6=H$1)*($D$2:$D$6<=$G2)*($E$2 :$E$6=$G2)*$C$2:$C$6) If actual data contains more names - add after 'Smith' and continue to copy... Hope this helps! "Brian" wrote: I have, for me, a very complicated scenario and I am hoping someone can help. I need to populate a table with the total number of work hours a person is responsible for on a given day. For example, I need to return the total number of work hours for Jones on 6/3/2008. My data looks like this: Task Name Work Hours Start Date End Date 1 Jones 2 6/2/2008 6/13/2008 2 Smith 1 5/26/2008 6/30/2008 3 Smith 0.5 6/4/2008 6/6/2008 4 Jones 5 5/30/2008 6/12/2008 5 Jones 0.5 6/9/2008 6/18/2008 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Summing
It is a pretty cool function... math magic?! :-)
Happy to help... "Brian" wrote: Magic, it works. I really need to learn this formula, thanks! "BoniM" wrote: With dates in column G and Jones in H1 and Smith in I1, copy this formula into H2 and then copy across and down to fill the table. =SUMPRODUCT(($B$2:$B$6=H$1)*($D$2:$D$6<=$G2)*($E$2 :$E$6=$G2)*$C$2:$C$6) If actual data contains more names - add after 'Smith' and continue to copy... Hope this helps! "Brian" wrote: I have, for me, a very complicated scenario and I am hoping someone can help. I need to populate a table with the total number of work hours a person is responsible for on a given day. For example, I need to return the total number of work hours for Jones on 6/3/2008. My data looks like this: Task Name Work Hours Start Date End Date 1 Jones 2 6/2/2008 6/13/2008 2 Smith 1 5/26/2008 6/30/2008 3 Smith 0.5 6/4/2008 6/6/2008 4 Jones 5 5/30/2008 6/12/2008 5 Jones 0.5 6/9/2008 6/18/2008 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Summing
Here you go... helping you to learn the magic!
The explanation of why sumproduct works for what you wanted: http://www.maysstuff.com/offdocs/hours.xls "Brian" wrote: Magic, it works. I really need to learn this formula, thanks! "BoniM" wrote: With dates in column G and Jones in H1 and Smith in I1, copy this formula into H2 and then copy across and down to fill the table. =SUMPRODUCT(($B$2:$B$6=H$1)*($D$2:$D$6<=$G2)*($E$2 :$E$6=$G2)*$C$2:$C$6) If actual data contains more names - add after 'Smith' and continue to copy... Hope this helps! "Brian" wrote: I have, for me, a very complicated scenario and I am hoping someone can help. I need to populate a table with the total number of work hours a person is responsible for on a given day. For example, I need to return the total number of work hours for Jones on 6/3/2008. My data looks like this: Task Name Work Hours Start Date End Date 1 Jones 2 6/2/2008 6/13/2008 2 Smith 1 5/26/2008 6/30/2008 3 Smith 0.5 6/4/2008 6/6/2008 4 Jones 5 5/30/2008 6/12/2008 5 Jones 0.5 6/9/2008 6/18/2008 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Summing | Excel Worksheet Functions | |||
| Conditional Summing... | Excel Worksheet Functions | |||
| Conditional Summing | Excel Discussion (Misc queries) | |||
| "Conditional" summing | Excel Worksheet Functions | |||
| conditional Summing | Excel Worksheet Functions | |||
Linear Mode
