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)

[table="width: 500, class: grid"]
[tr]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[td]E[/td]
[td]F[/td]
[/tr]
[tr]
[td]Total Earned[/td]
[td]Date Earned[/td]
[td]Date Expire[/td]
[td]Used[/td]
[td]Date Used[/td]
[td]Balance[/td]
[/tr]
[tr]
[td]4.5[/td]
[td]7/6/2017[/td]
[td]2/15/2018[/td]
[td]0[/td]
[td]0[/td]
[td]4.5[/td]
[/tr]
[tr]
[td]1[/td]
[td]8/2/2017[/td]
[td]3/14/2018[/td]
[td]0[/td]
[td]0[/td]
[td]5.5[/td]
[/tr]
[tr]
[td]9[/td]
[td]8/16/2017[/td]
[td]3/28/2018[/td]
[td]8[/td]
[td]08/26/2017[/td]
[td]6.5[/td]
[/tr]
[tr]
[td]5[/td]
[td]9/21/2017[/td]
[td]5/3/2018[/td]
[td]0[/td]
[td]0[/td]
[td]11.5[/td]
[/tr]
[/table]

 

[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.