Running a count with structured references is achievable in Google Sheets tables using the COUNTIF function. Here is the generic formula:
=COUNTIF(first:current, current)The key question is how to identify the first and current row values dynamically.
If your table, named Table1, has a column titled “Sales Platform” and you want to calculate a running count of sales platforms, whether the data is sorted or unsorted, the formula would look like this:
=COUNTIF(INDEX(Table1[Sales Platform], 1):SINGLE(Table1[Sales Platform]), SINGLE(Table1[Sales Platform]))Explanation:
first: Refers to the first value in the column, derived using the formula:INDEX(Table1[Sales Platform], 1)current: Refers to the value in the current row, extracted using:SINGLE(Table1[Sales Platform])
Example of Running Count with Structured Table References
In the example below, the table named Table1 occupies the range B2:D10.
To calculate the running count:
- Enter the formula in cell D3:
=COUNTIF(INDEX(Table1[Sales Platform], 1):SINGLE(Table1[Sales Platform]), SINGLE(Table1[Sales Platform])) - Drag the formula down from D3 to D10.
This calculates the running count of occurrences in the range B3:B10.
Using Array Formulas for Running Count with Structured References
You can also calculate the running count using an array formula. With this approach, you don’t need to separately extract the first and current cell values.
Generic formula:
=ArrayFormula(COUNTIFS(column, column, ROW(column), "<="&ROW(column)))For the example data:
=ArrayFormula(COUNTIFS(Table1[Sales Platform], Table1[Sales Platform], ROW(Table1[Sales Platform]), "<="&ROW(Table1[Sales Platform])))- Clear the range D3:D10.
- Enter this formula in cell D3.
This formula conditionally counts the values in the column up to the current row for each row.
Things to Know
Drag-Down Formula:
- When adding a new row at the bottom (e.g., in cell B11), simply enter data in B11. The table will automatically expand, and the formula in D10 will copy to D11.
- Avoid using the “+” button that appears when you hover over B10 to add a new row.
- To insert rows between existing records, use the Insert menu > Row below option. Here, you can also use the “+” button.
Array Formula:
- When adding a new row at the bottom, either use the “+” button or enter the data directly below the last row—both methods work.
- When inserting a new row between existing data, use the Insert menu command.
