Conditional Summing

[Brian]

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

 

[BoniM]

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]

Magic, it works. I really need to learn this formula, thanks!

 

[BoniM]

It is a pretty cool function... math magic?!
Happy to help...

 

[BoniM]

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