SUMIF nesting ???

[Dave]

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.

 

[Teethless mama]

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

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

 

[RagDyeR]

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!
=====================================================

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.

 

[Dave]

That works GREAT!!!
Thank you

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

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