How to do a cumulative running count of a value in a column

Ashleigh Cole

I need to count the number of times a value occurs in a column and have it do a running count of it in another column.

Example: I have a spreadsheet with a column (B) that will have numbers that will occur a varying number of times. In another column (A), I want it to count the number of times that a value shows up in column B and then number the rows that that number shows up in sequentially. So, if the number 123 occurs in column B 5 times, in rows 5-10, then I want column A row 5 to be 1, column A row 6 to be 2, column A row 7 to be 3, etc.

Additionally this will need to be an array or column formula, so the formula can't reference a specific value, I need it to look at the value in the row that it's in and run the formula on it. If the cell in column B in that row is blank then I don't want it to do anything.

I've attached a screenshot of what this would look like (I just entered the numbers manually to show what it should look like when the formula does it automatically)

In Google sheets, I do this with the formula ARRAYFORMULA(IF(LEN($B2:$B), COUNTIFS($B2:$B, $B2:$B, ROW($B2:$B), "<="&ROW($B2:$B)),)) but I haven't been able to figure out how to replicate this in this system.

Thank you

Kelly Moore

Hey @Ashleigh Cole

This approach will require an additional column, the system generated autonumber column, [Row ID] - if you don't already have it. You will need to SAVE the sheet once this column is inserted (if it wasn't already there). I am also assuming that new rows get added to the bottom of the sheet.

Formula for your Column A

=COUNTIFS(B:B, B@row, [Row ID]:[Row ID], @cell<=[Row ID]@row)

Will this work for you?

Kelly