SUMIF nesting ???
 

I have table as follows...

Person Date Time
Kelly 2/1/08 5
Wagner 3/4/08 5
Kelly 4/7/08 3
Wagner 4/10/08 1
Kelly 4/15/08 10
Kelly 4/18/08 3
Wagner 5/5/08 4
Kelly 6/2/08 2
Wagner 6/4/08 1


I am looking for A formula to calculate the Total Time in column C for Kelly
in column A and between dates = 2/1/08 and <= 6/1/08 in column B.


Results Table as follows
Person Start Date End Date Total Time
Kelly 2/1/08 6/1/08 ?? need formula ??

I can get totals for date range with...
=SUMIF(B2:B8,"="&B13,C2:C8)-SUMIF(B2:B8,""&C13,C2:C8)

and totals for person with...
=SUMIF(A2:A9,"=Kelly",C2:C9)

But need help with two problems...
1) I would like to replace "Kelly" with cell reference in the second formula
and then...
2) I would like to have just one formula that will give results as stated
above

Thanks in advance.

SUMIF nesting ???
 

A13: holds Kelly
B13: holds start date
C13: holds end date

=SUMPRODUCT(--(A2:A10=A13),--(B2:B10=B13),--(B2:B10<=C13),C2:C10)


"Dave" wrote:

I have table as follows...

Person Date Time
Kelly 2/1/08 5
Wagner 3/4/08 5
Kelly 4/7/08 3
Wagner 4/10/08 1
Kelly 4/15/08 10
Kelly 4/18/08 3
Wagner 5/5/08 4
Kelly 6/2/08 2
Wagner 6/4/08 1


I am looking for A formula to calculate the Total Time in column C for Kelly
in column A and between dates = 2/1/08 and <= 6/1/08 in column B.


Results Table as follows
Person Start Date End Date Total Time
Kelly 2/1/08 6/1/08 ?? need formula ??

I can get totals for date range with...
=SUMIF(B2:B8,"="&B13,C2:C8)-SUMIF(B2:B8,""&C13,C2:C8)

and totals for person with...
=SUMIF(A2:A9,"=Kelly",C2:C9)

But need help with two problems...
1) I would like to replace "Kelly" with cell reference in the second formula
and then...
2) I would like to have just one formula that will give results as stated
above

Thanks in advance.

SUMIF nesting ???
 

Say your datalist is in A1 to C10, with Row1 as a header row.

Say your results table is in E1 to H1, with headers in Row1.

Try this formula in H2:

=SUMPRODUCT((A2:A10=E2)*(B2:B10=F2)*(B2:B10<=G2)* C2:C10)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Dave" wrote in message
...
I have table as follows...

Person Date Time
Kelly 2/1/08 5
Wagner 3/4/08 5
Kelly 4/7/08 3
Wagner 4/10/08 1
Kelly 4/15/08 10
Kelly 4/18/08 3
Wagner 5/5/08 4
Kelly 6/2/08 2
Wagner 6/4/08 1


I am looking for A formula to calculate the Total Time in column C for Kelly
in column A and between dates = 2/1/08 and <= 6/1/08 in column B.


Results Table as follows
Person Start Date End Date Total Time
Kelly 2/1/08 6/1/08 ?? need formula ??

I can get totals for date range with...
=SUMIF(B2:B8,"="&B13,C2:C8)-SUMIF(B2:B8,""&C13,C2:C8)

and totals for person with...
=SUMIF(A2:A9,"=Kelly",C2:C9)

But need help with two problems...
1) I would like to replace "Kelly" with cell reference in the second formula
and then...
2) I would like to have just one formula that will give results as stated
above

Thanks in advance.

SUMIF nesting ???
 

That works GREAT!!!
Thank you

"Teethless mama" wrote:

A13: holds Kelly
B13: holds start date
C13: holds end date

=SUMPRODUCT(--(A2:A10=A13),--(B2:B10=B13),--(B2:B10<=C13),C2:C10)


"Dave" wrote:

I have table as follows...

Person Date Time
Kelly 2/1/08 5
Wagner 3/4/08 5
Kelly 4/7/08 3
Wagner 4/10/08 1
Kelly 4/15/08 10
Kelly 4/18/08 3
Wagner 5/5/08 4
Kelly 6/2/08 2
Wagner 6/4/08 1


I am looking for A formula to calculate the Total Time in column C for Kelly
in column A and between dates = 2/1/08 and <= 6/1/08 in column B.


Results Table as follows
Person Start Date End Date Total Time
Kelly 2/1/08 6/1/08 ?? need formula ??

I can get totals for date range with...
=SUMIF(B2:B8,"="&B13,C2:C8)-SUMIF(B2:B8,""&C13,C2:C8)

and totals for person with...
=SUMIF(A2:A9,"=Kelly",C2:C9)

But need help with two problems...
1) I would like to replace "Kelly" with cell reference in the second formula
and then...
2) I would like to have just one formula that will give results as stated
above

Thanks in advance.