Running Balance

[jvaughn0311]

The table below is an example of what I am working on. I need help with column F.

I can calculate a total balance using "Today()" in criteria:
=SUMIF(C:C,">="&TODAY(),A:A)

I can also easily calculate a running total in Column F without considering date expire to date used.
In Cell F2 it would be =A2-D2, then In Cell F3 the equation would be =F2+A3-D3

But I am stuck on how to create a running total in Column F while factoring date expire as data progress downward (ultimately at late dates in the future)

A	B	C	D	E	F
Total Earned	Date Earned	Date Expire	Used	Date Used	Balance
4.5	7/6/2017	2/15/2018	0	0	4.5
1	8/2/2017	3/14/2018	0	0	5.5
9	8/16/2017	3/28/2018	8	08/26/2017	6.5
5	9/21/2017	5/3/2018	0	0	11.5

 

[rpaulson]

use SUMIFS (note the extra S on the end) it allows for multiple criteria.

Ross

 

[jvaughn0311]

Thanks for the SUMIFS statement. Not seeing how that solves the problem though. Thanks again for your time in responding.

 

[rpaulson]

guess i don't follow 100%, if there is a value in column E then Remove the value in Column D from the running total?

if so then put this into F3 and fill down =F2+A3-IF(E3>0,D3)


Ross

 

[jvaughn0311]

That may work with a slight variation

=F2+A3-IF(E3 < C3,D3) - had to add spaces for data to appear


But since the values will expire over time maybe I have to have two separate calculations and not a simple "running total" approach.

Maybe it is better to split out the accumulated time using the "sumif today" equation:

=SUMIF(C:C,">="&TODAY(),A:A)

Then have another equation adding up the used time.

Like I said I am stumped on how to keep the additions of new time, minus the used time. Then factoring in unused expired time.