Sumifs rolling total - calculation not adding up

[msdreamer]

Hi,

I am trying to do a running total based on Date and Hours. The end result that I want (table A) is the "rolling hours" column.
Table A
DATE Person Hours Rolling Hours
Tue, 05/28/19 Peter Pan 12.06 12.06
Wed, 05/29/19 Peter Pan 4.97 17.03
Sat, 06/01/19 Peter Pan 12.10 29.13

The running total is not calculating right. Table B is what comes up from the formula I used: ={SUMIFS(C:C,A:A,Days!K2:K4,B:B,Days!L2:L4)}
Table B
DATE Person Hours Rolling Hours
Tue, 05/28/19 Peter Pan 12.06 12.06
Wed, 05/29/19 Peter Pan 4.97 4.97
Sat, 06/01/19 Peter Pan 12.10 12.10

Table C is the dates and person I pointed the formula too.
Table C
DATE Person
Tue, 05/28/19 Peter Pan
Wed, 05/29/19 Peter Pan
Sat, 06/01/19 Peter Pan

 

[Aryatect]

Hi msdreamer,

I think you can achieve what I understood by below, ie creating the formula in D2 and copying it down:

	A	B	C	D
DATE	Person	Hours	Rolling Hours
Tue, 05/28/19	Peter Pan
Wed, 05/29/19	Peter Pan
Sat, 06/01/19	Peter Pan

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]12.06[/TD]
[TD="align: right"]12.06[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]4.97[/TD]
[TD="align: right"]17.03[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]12.1[/TD]
[TD="align: right"]29.13[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=SUM($C$2:C2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=SUM($C$2:C3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]=SUM($C$2:C4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Did I correctly understood your problem or am I missing something.

Let me know!

 

[msdreamer]

Hi msdreamer,

I think you can achieve what I understood by below, ie creating the formula in D2 and copying it down:

	A	B	C	D
DATE	Person	Hours	Rolling Hours
Tue, 05/28/19	Peter Pan
Wed, 05/29/19	Peter Pan
Sat, 06/01/19	Peter Pan

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]12.06[/TD]
[TD="align: right"]12.06[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]4.97[/TD]
[TD="align: right"]17.03[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]12.1[/TD]
[TD="align: right"]29.13[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]=SUM($C$2:C2)[/TD]
[/TR]
[TR]
[TH]D3[/TH]
[TD="align: left"]=SUM($C$2:C3)[/TD]
[/TR]
[TR]
[TH]D4[/TH]
[TD="align: left"]=SUM($C$2:C4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Did I correctly understood your problem or am I missing something.

Let me know!

I simplified the problem by giving 1 person's name. I actually have 150 names with dates for 4 weeks that I need to calculate rolling totals. This was why I was looking at using sumifs to approach the problem.

I updated the scenario with the table D below
Table D.

	A	B	C	D
DATE	Person	Hours	Rolling Hours
Tue, 05/28/19	Peter Pan
Wed, 05/29/19	Peter Pan
Sat, 06/01/19	Peter Pan
Tue, 05/28/19	Joe
Wed, 05/29/19	Joe
Sat, 06/01/19	Joe

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]12.06[/TD]
[TD="align: right"]12.06[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]4.97[/TD]
[TD="align: right"]17.03[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]12.1[/TD]
[TD="align: right"]29.13
[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]

</tbody>

Thanks!