I have several formula options for performing a running count in Google Sheets, whether it be for a sorted or unsorted range. In this article, I will explain both non-array and array formulas that utilize functions such as MATCH and COUNTIF/COUNTIFS. So, let’s dive in and explore these formulas together!
Running Count of a Single Item in Google Sheets
In a list, you may want to know the running count of a single item or multiple items. For each occurrence of the item, the formula will assign a sequential number, starting from 1 and incrementing with each subsequent occurrence. Here’s how you can calculate the running count, also known as the cumulative count, in Google Sheets.
In the example below, I will find the cumulative count of the item “Apple” using the IF and COUNTIF functions:
=IF(A2:A="Apple", COUNTIFS(A$2:A2,"Apple"),"")
This formula is widely used not only in Google Sheets but also in Excel. It returns the sequential numbers for each occurrence of the item “Apple”.
If you have two columns in the cumulative count calculation, you can combine the values using the ampersand sign and include the ArrayFormula function:
=IF(A2:A&B2:B="FrankensteinMary Shelly", ARRAYFORMULA(COUNTIFS(A$2:A2&B$2:B2,"FrankensteinMary Shelly")), "")
If you’re looking for an array formula solution for the single-item running count based on single or double columns, then here it is!
Cumulative Count of a Single Item – Array Formula
The array formula for the cumulative count of a single item in a list is based on the IF and COUNTIFS functions. To cover infinite ranges, you can replace A2:A10 with A2:A.
Here’s the formula:
=ARRAYFORMULA(IF(A2:A10="Apple", COUNTIFS(A2:A10,A2:A10,ROW(A2:A10),"<="&ROW(A2:A10)),""))Are you interested in understanding how this formula works? Let’s take a quick look at the COUNTIFS syntax:
COUNTIFS(criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])
In this formula, I execute the COUNTIFS function whenever the item “Apple” occurs in the rows. The IF statement handles this condition effectively. As you can see from the list, the item “Apple” occurs five times. Therefore, the COUNTIFS formula assigns the number 5 to each row that contains “Apple”.
If you only have one criterion in the COUNTIFS formula, the second criterion (ROW function) plays a crucial role. Here’s the condition:
ROW(A2:A)<="&ROW(A2:A)This condition remains TRUE up to the current row, rather than the following row, limiting the COUNTIFS function to count up to the current row only.
For multiple columns, you can use the following formula:
=ARRAYFORMULA(IF(A2:A10&B2:B10="FrankensteinMary Shelly", COUNTIFS(A2:A10&B2:B10,A2:A10&B2:B10,ROW(A2:A10),"<="&ROW(A2:A10)),""))Now, let’s move on to calculating the cumulative count of multiple items in a list.
Running Count of Multiple Items in Google Sheets
To find the running count of all the items in a list, you can use the Countifs Array Formula mentioned above. However, please note that this formula works only for a sorted range. Don’t worry though, I also have a solution for an unsorted range.
Cumulative Count of All Items in a Sorted List (Works Well in Unsorted List Also)
Single Column (Array Formula)
For example:
=ARRAYFORMULA(COUNTIFS(A2:A10,A2:A10,ROW(A2:A10),"<="&ROW(A2:A10)))
In this case, the sequential count restarts whenever there is a change in value in Column A.
Multiple Columns (Array Formula)
=ARRAYFORMULA(COUNTIFS(A2:A7&B2:B7,A2:A7&B2:B7,ROW(A2:A7),"<="&ROW(A2:A7)))
In this tutorial, I aim to provide you with different formula options for a running count. So here’s one more formula based on the MATCH function for a sorted range (single column):
=ARRAYFORMULA(ROW(A2:A10)-MATCH(A2:A10,A2:A10,0))If you would like a formula explanation for this example, you can find it in the tutorial “Group Wise Serial Numbering in Google Sheets”.
Now, what about the running count of an unsorted list? Let’s explore that too!
Running Count of All Items in an Unsorted List
Running count is possible in an unsorted list as well, and once again, we can rely on the MATCH function. Here’s the generic formula:
=SORT(ROW - MATCH[sorted match], sorted row, true)And here’s the specific formula:
=SORT(ROW(D2:D10)-MATCH(SORT(D2:D10),SORT(D2:D10),0),SORT((ROW(D2:D10)-ROW(D2)+2),D2:D10,1),1)
For an explanation of this formula, please refer to the “How to Find Multiple Occurrences of Rows in Google Sheets” tutorial.
To explore similar topics, you can check out the following articles:
- Running Total Array Formula in Excel [Formula Options]
- Reverse Running Count Simplified in Google Sheets
- Fix Interchanged Names in Running Count in Google Sheets
I hope you found these formula examples helpful for calculating the running count in Google Sheets. If you want to learn more tips and tricks, visit Crawlan.com for a wealth of information.
