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

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

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

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

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