COUNTIF value of everything before current row.
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)
Best Answer
-
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 :)
Answers
-
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 :)
-
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.
-
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
-
Thank you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!