To populate a column with the running count of multiple values present in another column, there is no quick solution. But I do have one array formula which is a combination of MATCH and SORT functions.
Of course, there are other functions involved. But the most prominent ones are the above two functions.
To get the running count of occurrence of a single value in a list, you can use a relatively simple COUNTIF based formula.
Simple Running Count Formula in Google Sheets:
=IF(A2="Apple",COUNTIF($A$2:A2,"Apple"),"")
This formula is in cell B2, then dragged down.
This formula only returns the running count of a single value. There is one more drawback. This is not an array formula. So you must copy paste the formula downwards.
But what I am providing you is a powerful array formula that I have used in column C in the above example.
The Formula to Populate the Running Count of Multiple Values in a List in Google Sheets
I have entered the following formula in cell C2 in the above example.
=Array_Constrain(iferror(sort({row(A2:A)-row(A2)+2-match(sort(A2:A),sort(A2:A),0),SORT(ROW(A2:A)+2-row(A2)+2,A2:A,1)},2,1),),9^9,1)
Note: Learn Google Sheets Functions.
This formula returns the running count of all the items in the list. This is self-expanding so enter this formula only in the cell C2 (as per the example above) and prior to that keep the cells below it blank.
This Google Sheets running count formula supports infinite ranges. Now here is a new example.
In this, the formula is in cell B2. There are no changes in the formula.
I will explain how to modify the above formula to accommodate any column other than Column A.
How to Use this Array Formula to Populate Running Count of Occurrences of Values
I know your list to find the running count may be in a different column. So I should explain to you how to tweak this formula.
In the first formula, the list is in the range A2: A. So you can see the reference A2: A everywhere in that formula.
Here the range is B4: B. So just replace A2: A with B4: B. That is the only changes that you should make.
That’s all about the running count of multiple values in Google Sheets. Enjoy!
Hi, there,
I have a question, and I think I cannot find the answer here.
How do I create the group number based on the “Code” given? Notice there are repeating sets.
WO|CODE|GROUP
961400279176|2|1
961400279176|3|1
961400279176|5|1
961400279187|2|2
961400279187|3|2
961400279187|5|2
Hi, Desmond Lee,
I guess you want to get the group number in column C.
In cell C2, try this formula.
=ArrayFormula(FLATTEN(split(rept(sequence(count(B2:B)/3)&"|",3),"|")))If that doesn’t help, leave the URL of your sample sheet below (in reply).
Hi, Desmond Lee,
Thanks for sharing a sample Sheet.
I’ve inserted my formula in cell M1 in a new tab named “test.”
I’ve also left some notes.
Hi Prashanth,
Thanks so much. Column C is raw data from the user input, and it cannot be modified.
How would you implement this same formula, but based on another set of criteria? For example, I want to have a running count of the times “Apple” occurred when column X equals May 2021, and so on?
Hi, Kauri Voss,
First I thought you require to use a conditional running total formula.
But I realized that you want to summarize data using SUMIF and Vlookup as detailed below.
How to Exclude Duplicates in Vlookup Array Result in Google Sheets.
Entered the required formula in cell C2 in your shared sheet.
Hello, Prashanth,
I’ve been looking for a solution to this problem all over the place and I couldn’t find until I came across your post.
It’s exactly what I’m looking for but for some reason I can’t get the formula to work.
I even tried to copy the example exactly as it shows, with the fruit names on column A and the formula on column C.
At first, it just gave me a FORMULA PARSE ERROR, and I noticed that the very last mention of A2:A doesn’t light up in color like the rest of range references in the formula.
I changed the commas in the formula for semicolons and it lit up but then the formula would show a #REF error with the message “RESULT WAS NOT AUTOMATICALLY EXPANDED, PLEASE INSERT MORE ROWS”….
Do you have any suggestions?
Hi, Federico,
The comma to semicolon issue is there because of the sheets locale settings (File menu > Spreadsheet settings). My sheets’ locale may be set to the UK and I assume yours are set to one of the EU countries.
How to Change a Non-Regional Google Sheets Formula
Regarding the problem, I have later written one more detailed tutorial on the same topic which contains a more simple formula using COUNTIF.
Just search the keyword “running count” to find the tutorial. The search field is available on the top navigation bar.
If that doesn’t solve your problem, feel free to share your sheet with me. I’ll try to sort out the issue for you.