Fixed running total

[bizmark222]

Hi
I know how to total sum cells together but-
I need for example, if f5,f6,f7 = 1 (f5 has 1 in it) and i change the value in f5 back to 0 the sum total stay fixed at 1, so if f6 was to show 3 it would =4 and keep an ongoing total regardless of numbers changing back to 0 in above cells, make sense?

 

[steve the fish]

Thats not how spreadsheets work im afraid. If you wanted to keep a total somewhere you could use code but a formula isnt going to do it.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range, c As Range

Set rng = Intersect(Range("F5:F7"), Target)

If Not rng Is Nothing Then
    For Each c In rng
        If IsNumeric(c) Then
            Range("F8").Value = Range("F8").Value + c.Value
        End If
    Next
End If

End Sub

Place that in the worksheet module. Right click sheet tab. View code. Paste in the code in the white space.

 

[bizmark222]

Thank you for your help!
it works but the values in F5-F7 have a formula in them that triggers a 1 for true and 0 for negative.
Its these that I need a fixed running total for. The values do change but F8 will not add them up, is there any way around this?

 

[steve the fish]

What is the formula?

 

[bizmark222]

What is the formula?

=IF(E5<1.6,1,0)
=IF(E6<1.6,1,0)
=IF(E7<1.6,1,0)

thanks again

 

[Fluff]

Are the values in E5:E7 hard values or formulae?

 

[bizmark222]

Are the values in E5:E7 hard values or formulae?

formula too

 

[Fluff]

Are those formulae looking at other formulae, or hard values & are they looking at cells on another sheet/workbook?

 

[Fluff]

Cross posted https://www.excelforum.com/excel-formulas-and-functions/1279807-fixed-running-total.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[bizmark222]

Are those formulae looking at other formulae, or hard values & are they looking at cells on another sheet/workbook?

They are looking at hard values on same sheet, thanks