COUNTIF value of everything before current row.

BrianThy

I am trying to count how many times a name appears in a column on a rolling basis. As the names get added, I want it to count upwards.

Here's an example with 2 names:

Name	Count
Brian	1
Michael	1
Brian	2
Brian	3
Michael	2
Brian	4
Brian	5
Brian	6
Michael	3

My Current Formula which just counts the total # of times the name appears:

=COUNTIF([Name]:[Name], [Name]@row)

Gillian C

Hi @BrianThy

If you are wanting to count everything above the row you are on then you will want to use a cell formula rather than the column formula you have above.

Try

=COUNTIF([Name]$1:[Name]@row,[Name]@row)

The above basically means instead of the range being the whole column [Name] it is now from the first cell in the Name column ([Name]$1) until the current cell in the row you are on [Name]@row.

Hope that works and helps :)

Gillian C

Hi @BrianThy

If you are wanting to count everything above the row you are on then you will want to use a cell formula rather than the column formula you have above.

Try

=COUNTIF([Name]$1:[Name]@row,[Name]@row)

The above basically means instead of the range being the whole column [Name] it is now from the first cell in the Name column ([Name]$1) until the current cell in the row you are on [Name]@row.

Hope that works and helps :)

BrianThy

Wow Thanks! That works!

Is there a way to have it automatically apply that formula to a newly added row? Basically acting as a column function.

Gillian C

Hi @BrianThy

Super glad it helped. Cell formula will automatically copy into a new row if the two rows above the new row contain the formula.

So if you put it in the first row of the column you are adding the formula to you can drag the cell down to all your current entries (minimum of two rows) then the formula will then automatically copy when you add a new row. Dragging the cell just like in excel by dragging the bottom left corner

BrianThy

Thank you so much!