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

#### Jonbenjamin06

##### New Member
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.

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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:

Code:
``````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.

Last edited:
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

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:

Code:
``````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!

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:

Code:
``````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.

Last edited:

Replies
5
Views
165
Replies
5
Views
303
Replies
0
Views
561
Replies
1
Views
96
Replies
7
Views
316

1,196,296
Messages
6,014,521
Members
441,826
Latest member
roudarreza

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back