How to do a running count in a single cell when input is another single cell using =countif

[Jonbenjamin06]

I need help with something i can't find on google. I need a video demonstrating this dilemma. I want to create a running total in a single cell. The input cell is a cell that changes by =countif( function. Basically, I'm trying to tally how many times my account goes negative throughout the year in an investment account. I'd like it to add up the numbers that show up in a single cell. I have 12 sets it needs to run through and every time my "Went Negative" cell increases in count. I'd like it to automatically be added in another cell. So basically, if my input cell is =countif as each set (month) comes and goes I'd like another cell to be tallying the total times it dips. Set 1 in F5 = Countif counts 2 times it goes negative in month 1. so in D37 it puts 2. Then in set 2 =Countif finds it goes negative 4 times for that month so D37 Becomes 6. Make sense? How do I do this?

Sorry. Lets say I run a random data set in column A1:A60. =Choosebetween(A1:A60,"Red","Green"). It'll populate that column with red and green. I have a separate cell counting how many times green comes up. Next random selection it'll change, right? What i'm trying to do is populate column A data 12 times in same column and just have =countif count the data i need immediately in the current set. since =countif will keep changing, I wont get a running count at the end of 12 sets. In order to keep a tally of all my greens for the 12 sets I'd like another cell to be a cumulative output of my =countif function. So everytime the counter comes up, the cumulative cell is adding through the sets as =countif is totaling the data in current set. Make sense? So one cell is counting the data in column A another cell will keep track of all the counts =countif made throughout the exercise. Phew.

 

[JustynaMK]

Hi Jonbenjamin06,

Not sure what kind of Excel function can help you, but hopefully the VBA code below will prove useful. Please paste it into the Sheet1 macro view:

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Application.Intersect(Range("[B]A1:A60[/B]"), Range(Target.Address)) Is Nothing Then
    Range("[B]C3[/B]").Value = Range("[B]C3[/B]").Value + Range("[B]C1[/B]").Value
End If


End Sub

Let's assume you have a random selection of "red" and "green" in cells A1:A60, and a simple countif formula in cell C1 ( =COUNTIF(A:A,"green") ). What will happen is that every time you change anything in a range A1:A60, a value in cell C3 will automatically add the new result of countif formula (cell C1). Hopefully this is a good starting point.

 

[WarfritLive]

So if I have this right, colum A is whatever data that changes often. Easiest way is a helper column. Let's say the count is in B1 and let's use the example results of 2, 1, 3, 5, 6.
If your helper column is in C:
C1=IF(C1="",B1,C1)
C2=IF(C2="",IF($B$1<>C1,$B$1,""), C2)
C3=IF(C3="",IF($B$1<>C2,$B$1,""), C3)
Etc.

This adds B1 to the list every time it changes, then you can add those up as you please in another cell. This won't count B1 if the data changes but the count comes out to the same number. Easiest way around that is to clear the column every time, resetting B1 to 0

 

[Jonbenjamin06]

Hi Jonbenjamin06,

Not sure what kind of Excel function can help you, but hopefully the VBA code below will prove useful. Please paste it into the Sheet1 macro view:

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Application.Intersect(Range("[B]A1:A60[/B]"), Range(Target.Address)) Is Nothing Then
    Range("[B]C3[/B]").Value = Range("[B]C3[/B]").Value + Range("[B]C1[/B]").Value
End If


End Sub

Let's assume you have a random selection of "red" and "green" in cells A1:A60, and a simple countif formula in cell C1 ( =COUNTIF(A:A,"green") ). What will happen is that every time you change anything in a range A1:A60, a value in cell C3 will automatically add the new result of countif formula (cell C1). Hopefully this is a good starting point.

Thank you. Here's the thing. I'm not VBA savvy and it doesn't work when I enter it. Can you demonstrate this on video? I'll give you the cells i'm trying to run so they can be automatically added. I've been racking my brain on this all day.

F5 is my cell for =countif
G5 is my cell for tallying all the counts F5 finds throughout the sets. Please help with this code!

 

[JustynaMK]

Apologies, I never recorded a video and I don't know how to do it

Here's what you can do:

1. Open an empty Excel file and save it on desktop

2. At the bottom of the Excel application you see 3 sheets: right click on Sheet1, View Code, and paste the following code into the "Microsoft Visual Basic" screen:

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Application.Intersect(Range("A1:A60"), Range(Target.Address)) Is Nothing Then
    Range("G5").Value = Range("G5").Value + Range("F5").Value
End If


End Sub

3. Close the "Microsoft Visual Basic" window. Go back to your Excel file (Sheet1) and create the following formula in cell F5:
=COUNTIF(A:A,"green")

4. Now you will notice that once you apply some changes to cells in range A1:A60, cell G5 will start changing its value and will increase every time formula in F5 recalculates its results.

Hope it helps. Sorry for the inconvenience - please let me know if you have troubles with any of these steps.