Running Count in Google Sheets

I have different formula options for running count in Google Sheets. To perform running count I have non-array as well as array formulas. That also for sorted as well as unsorted range.

The said formulas are based on MATCH, COUNTIF/COUNTIFS. Both are easy to understand.

The running count and the cumulative count are the same. So I may have used both terms in my writing.

As a side note, if you are looking for a running total, then here is that tutorial – Normal and Array-Based Running Total Formula in Google Sheets.

Running Count of a Single Item in Google Sheets

In a list, you can get the running count of a single item or multiple items. For each occurrence of the item, the formula leaves a sequential number.

That means, the first occurrence will be numbered as 1, then 2, and so on. Let us see how to calculate running count aka cumulative count in Google Sheets.

In the below list I am finding the cumulative count of the item “Apple” using the functions IF and COUNTIF.

Running Count of Single Item – Non-Array Formula

Single Column (non-array):

No doubt it’s a non-array formula. That means the formula in cell C2 is to be dragged/copied down.

=if(A2:A="Apple",COUNTIFS(A$2:A2,"Apple"),)

The above formula example is the most commonly used running count formula not only in Google Sheets but also in Excel.

The formula returns the sequential numbers in each occurrence of the item “Apple”.

Multiple Columns (Non-Array):

If there are two columns in the cumulative count calculation, combine the values in the columns using the ampersand sign as below. Also, include the ArrayFormula function.

=if(A2:A&B2:B="FrankensteinMary Shelly",arrayformula(COUNTIFS(A$2:A2&B$2:B2,"FrankensteinMary Shelly")),)

Cumulative Count - multiple columns but single item

If you are looking for an array formula solution to the above single item running count based on values in single as well as double columns, here is that.

Cumulative Count of Single Item – Array Formula

Single Column (Array):

The array formula for the cumulative count of a single item in a list is based on the functions IF and COUNTIFS.

Please check my Functions Guide to refer to the functions used in this tutorial.

Formula:

=ARRAYFORMULA(if(A2:A10="Apple",COUNTIFS(A2:A10,A2:A10,ROW(A2:A10),"<="&ROW(A2:A10)),))

In this, if you want to cover infinite ranges, replace A2:A10 with A2:A.

Are you interested to know how this formula works?

To understand this running count array formula take a quick look at the COUNTIFS syntax.

COUNTIFS(criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])

I have executed the COUNTIFS in the rows wherever the item “Apple” occurs. The IF part at the beginning of the formula does that well.

As you can see on the list, the item “Apple” occurs 5 times. The below COUNTIFS formula places the number 5 in each row that contains “Apple”.

=ARRAYFORMULA(if(A2:A="Apple",COUNTIFS(A2:A,A2:A),))

In this formula, I have used one criterion in the COUNTIFS. Actually, the second criterion which is the ROW function is the important one. Here is that condition.

ROW(A2:A),"<="&ROW(A2:A)

This condition will be TRUE up to the current row not up to the next row. So it limits the COUNTIFS to count up to the current row, not as a whole.

Multiple Columns (Array):

For example data, please see the second screenshot above.

=ARRAYFORMULA(if(A2:A10&B2:B10="FrankensteinMary Shelly",COUNTIFS(A2:A10&B2:B10,A2:A10&B2:B10,ROW(A2:A10),"<="&ROW(A2:A10)),))

Time to move to how to get the cumulative/running count of multiple items in a list.

Running Count of Multiple Items in Google Sheets

To return the running count of all the items in a list in Google Sheets, you can use the just above Countifs Array Formula. But there is ~~one constraint~~! What’s that?

The formula will work ~~only in a sorted range~~. But don’t worry, I have the formula for an un-sorted range too.

Update: The same formula below will work in an unsorted range too.

Cumulative Count of All the Items in a Sorted List

(Works Well in Unsorted List Also)

Single Column (Array):

Example:

=ARRAYFORMULA(COUNTIFS(A2:A10,A2:A10,ROW(A2:A10),"<="&ROW(A2:A10)))

This time we can remove the IF statement.

Here the sequential count restarts in every value changes in Column A.

Multiple Columns (Array):

=ARRAYFORMULA(COUNTIFS(A2:A7&B2:B7,A2:A7&B2:B7,ROW(A2:A7),"<="&ROW(A2:A7)))

Cumulative Count - multiple columns all items

My aim with this tutorial is to give you different formula options for running count. So here is one more formula based on the MATCH function for the sorted range (single column).

=ArrayFormula(row(A2:A10)-match(A2:A10,A2:A10,0))

In the above example, you can use this formula in cell B2. I am leaving the formula explanation this time as you can find the same here – Group Wise Serial Numbering in Google Sheets.

So what about the running count of an unsorted list (alternative formula)?

Running Count of All the Items in an Un-sorted List

Running count is possible in an unsorted list too. Here again, we can depend on the MATCH function.

Generic Formula:

=sort(row - match[sorted match],sorted row,true)

Formula:

=Sort(row(D2:D10)-match(sort(D2:D10),sort(D2:D10),0),sort((row(D2:D10)-row(D2)+2),D2:D10,1),1)

Cumulative Count in un-sorted range Google Sheets

For the explanation of this formula, please just see the last part of this tutorial – How to Find Multiple Occurrences of Rows in Google Sheets.

Similar:

21 COMMENTS

Scott Michael March 14, 2024 At 12:03 pm

Hi Prashanth,

I’m currently working on setting up a rolling sequential number for purchase orders. I’ve developed a macro that automatically clears specific contents within the order.

I’m attempting to write a formula that, when I click the “New Order” button, deletes the desired data and advances the number to the next sequential value on the order form. However, I’m encountering difficulty in ensuring that the purchase order number updates to the next available sequential number.

Thanks Scotty

Fatima July 17, 2022 At 10:01 pm

I’m trying to find a running count of the number of times a customer has ordered, with their phone number being the customer identifier.

The issue here is that my data input software is glitchy, and sometimes a duplicate order shows up in my file.

How it is:

Order ID | Customer Phone # | Running Count (formula column)
123 | 111111111 | 1
123 | 111111111 | 2
456 | 222222222 | 1

Customer 111111111 only placed one order, but their duplicate order makes it seem as if they’ve ordered twice.

Fortunately, I have order IDs, which are unique to every order placed.

I was wondering, if there is some way to add to the formula where the duplicate orders are ignored?

How I want it to look like:

Order ID | Customer Phone # | Running Count (formula column)
123 | 111111111 | 1
123 | 111111111 |
456 | 222222222 | 1

My purpose is to get accurate re-ordering insights. I’ve stuck on this for a long time. Any help would be highly appreciated!

- Prashanth July 19, 2022 At 8:54 am
  
  Hi, Fatima,
  
  I clearly understand the problem. You can solve it effectively.
  
  If the running count of IDs is equal to 1, rerun the running count of phone numbers, else blank.
  
  This formula in cell C2 may return your expected result if the above data is in A1:B, where A1:B1 contain field labels.
  
  =ArrayFormula( if(countifs(row(A2:A),"<="&row(A2:A),A2:A,A2:A)=1, countifs(row(A2:A),"<="&row(A2:A),B2:B,B2:B),) )
  
  As a side note, we can use COUNTIFS also in an unsorted list.
  
Kiran Suresh June 13, 2022 At 3:56 pm

Hello,

I have a google sheet with Dates in Column A, File Codes in Column B example:

20 January 2020 | D87
18 February 2020 | D87
20 February 2020 | C49
01 March 2020 | D51
31 March 2020 | D51

I want to generate File names according to the Financial Year in Column C.

Example:

20 January 2020 | D87 | A-20D87001
18 February 2020 | D87 | A-20D87002
20 February 2020 | C49 | A-20C49001
01 March 2020 | D51 | A-20D51001
31 March 2020 | D51| A-20D51002

I’m stuck at the last digit. What formula should I apply to get running counts?

- Prashanth June 14, 2022 At 2:19 pm
  
  Hi, Kiran Suresh,
  
  I don’t clearly understand your question. You may try this first. If it doesn’t work as expected, share the URL of your sample sheet in your reply below.
  
  Assume the first row contain titles (column names), and you want the file names in C2:C.
  
  In D2, insert the following running count array formula and see the result.
  
  =ArrayFormula(if(len(B2:B),text(COUNTIFS(year(A2:A)&B2:B,year(A2:A)&B2:B,row(B2:B),"<="&row(B2:B)),"000"),))
  
  - Kiran Suresh June 14, 2022 At 9:42 pm
    
    Hello,
    
    I tried using the above formula, but I think I did something wrong. So I’m sharing the URL of the sample spreadsheet:
    
    – URL of the sample sheet removed by admin –
    
    - Prashanth June 15, 2022 At 11:48 am
      
      Hi, Kiran Suresh,
      
      The issue was in the year part. I have used column G to specify the corresponding financial year and used that column in my new formula.
      
      ={"File Name";ArrayFormula(if(len(C3:C),"A"&right(G3:G,2)&C3:C& text(COUNTIFS(G3:G&C3:C,G3:G&C3:C,row(C3:C),"<="&row(C3:C)),"000"),))}
      
      Please check your sheet for my new tab named test_infoinspired.
      
      - Kiran Suresh June 15, 2022 At 3:47 pm
        
        Wow!.. Thank you so much Sir..!! Thanks a lot.. 🙂
Ryan August 11, 2021 At 5:54 am

ROW(A2:A),"<="&ROW(A2:A)

Could you please explain this part more? I understand that it creates a counter for each item in a list, but how does it work.
Thanks

- Prashanth August 11, 2021 At 6:11 pm
  
  Hi, Ryan,
  
  It’s actually a simple logical test but ‘difficult’ to explain.
  
  The logical test returns TRUE up to the current row from the first row in the range.
  E.g.
  A2 – TRUE
  A2 – TRUE, A3 – TRUE
  A2 – TRUE, A3 – TRUE, A4 – TRUE
  … and so on.
  
Valo January 21, 2021 At 1:09 pm

Hi Prashanth,

I have a table with 2 columns:

Col1 – list of names and class (e.g. “John History”, “Billy Math”)
Col2 – list of grades

What I need to be able to do is rank the scores for each student.

Example of data:
John History | 60
John Math | 50
John Algebra | 70
John Physics | 20
Billy History | 65
Billy Math | 20
Billy Algebra | 90
Billy Physics | 100

Can you please assist?

- Prashanth January 22, 2021 At 10:35 am
  
  Hi, Valo,
  
  I assume the names are in A2:A (A1 contains the label “Name”) and B2:B contains the scores (B1 contains the label “Scores”).
  
  Solution:
  
  I have three solutions.
  
  The formula for cell C2 using the RANK function:
  
  =ArrayFormula(IFNA(rank(B2:B,B2:B,1)))
  
  If two persons have the same score, the formula will return the same rank.
  
  Eg.:
  
  Both John Physics and Billy Math scored 20. So their rank will be 1.
  Since the next rank of the person will be 3, not 2.
  
  The formula for cell D2 using the RANK.AVG function:
  
  =ArrayFormula(IFNA(RANK.AVG(B2:B,B2:B,1)))
  
  It follows the same above ranking principles, except the rank will be 1.5 (average).
  
  The formula for cell E2 using a Custom Formula:
  
  =ArrayFormula(IFNA(vlookup(A2:A,{index(sort(A2:B,2,1),0,1), ArrayFormula(if(len(B2:B),match(index(sort(A2:B,2,1),0,2), unique(index(sort(A2:B,2,1),0,2)),0),))},2,0)))
  
  Here the ranks of John Physics and Billy Math will be 1. But the next rank of the person will be 2, not 3.
  
  Pick the formula that is suitable for you.
  
Daniel July 31, 2020 At 3:20 am

I have a Google Sheets with last names in column B and first names in column C.

I would like to have a count in column E of each time a combination of last and first name appear together.

I was able to get the count working using =countifs(B:B, B2,C:C, C2)

However, I would 2nd and 3rd times the name combinations appear to demonstrate 2 and 3 instead of all showing the current count.

- Prashanth July 31, 2020 At 11:34 am
  
  Hi, Daniel,
  
  You can combine the first and last names and use the running count as below in E2.
  
  =ARRAYFORMULA(if(len(B2:B),COUNTIFS(B2:B&C2:C,B2:B&C2:C,ROW(A2:A),"<="&ROW(A2:A)),))
  
  This formula will populate the running count of the last and first names in E2:E. So, before inserting the formula, make E2:E blank.
  
Joshua Mark July 14, 2020 At 10:13 pm

Thanks a lot, I was stuck on finding the cumulative count of all items in the lists. This helped me a lot.

Sohail Merchant April 9, 2020 At 9:44 pm

Hi Prashanth,

I am trying to do a running count of a single item in google sheets, similar to your “Running Count of Single Item – Non-Array Formula” example.

I want the sequential count to be in column A, and I want it to count all the sections in column B that end with the words “blog post”.

Ex:
Column (A) | Column (B)

1 | blah blah blog post.
– | test string.
2 | learning google sheets blog post.
3 | need help with the sheets blog post.
– | chapter 8 notes.

I pasted =if(B2:B="*blog post*",COUNTIFS(B$2:B,"*blog post"),) into A2, but nothing is showing up. Where am I going wrong?

- Prashanth April 10, 2020 At 11:27 am
  
  Hi, Sohail Merchant,
  
  There are two issues in your partial matching running count formula.
  
  1. The wildcard use B2:B="*blog post*" is wrong.
  2. Inside the COUNTIFS (you can also use COUNTIF) instead of B$2:B you must use B$2:B2.
  
  Here is the proper formula to use in your case.
  
  =iferror(if(search("blog post",B2:B)>0,COUNTIFS(B$2:B2,"*blog post*"),))
  
  - Sohail Merchant April 12, 2020 At 11:49 pm
    
    How do I go about making this formula dynamic? Do I turn it into an array by using “arrayformula”? When I tried using array formula it just made every item that ends with ‘blog post’ the same number.
    
    - Prashanth April 13, 2020 At 1:18 pm
      
      Hi, Sohail Merchant,
      
      In partial matching, using ArrayFormula alone won’t work. You may please try this one.
      
      =ARRAYFORMULA(IFERROR(if(search("blog post",B2:B10)>0=TRUE,COUNTIFS(search("blog post",B2:B10)>0,search("blog post",B2:B10)>0,ROW(B2:B10),"<="&ROW(B2:B10)),)))
      
xray0 April 23, 2019 At 2:24 am

=ARRAYFORMULA(COUNTIFS(A2:A10,A2:A10,ROW(A2:A10),"<="&ROW(A2:A10))) can handle even unsorted list.

- Prashanth April 23, 2019 At 11:07 am
  
  Good catch!
  
  Thanks.