Formula
Hello,
I am trying to find a formula that will allow me to have a count of running occurrences. For example, on the first occurrence of “A”, a 1 will show, then 2 for the second; 3 for the third occurrence, and so on. I know in Excel I can use COUNTIF, but when I try it in Smartsheet it returns the total number of occurrences.
Best Answer
-
I would still use a COUNTIF for this, but you'll want to adjust your range so that it doesn't count past the current row.
For example, if you use:
=COUNTIF(Product:Product, "Honey")
This will return the number of cells that contain Honey in the entire column. Instead, adjust your Range to be from the top row of the sheet to the current row, like so:
=COUNTIF(Product$1:Product@row, "Honey")
In your case, because you have multiple criteria to search for, instead of typing "Honey" you could say Product@row, like this:
=COUNTIF(Product$1:Product@row, Product@row)
Then you can drag-fill the formula down the column. As long as there are two rows that contain the formula, the next row added will auto-fill this formula as well.
Let me know if this makes sense and helps!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
I would still use a COUNTIF for this, but you'll want to adjust your range so that it doesn't count past the current row.
For example, if you use:
=COUNTIF(Product:Product, "Honey")
This will return the number of cells that contain Honey in the entire column. Instead, adjust your Range to be from the top row of the sheet to the current row, like so:
=COUNTIF(Product$1:Product@row, "Honey")
In your case, because you have multiple criteria to search for, instead of typing "Honey" you could say Product@row, like this:
=COUNTIF(Product$1:Product@row, Product@row)
Then you can drag-fill the formula down the column. As long as there are two rows that contain the formula, the next row added will auto-fill this formula as well.
Let me know if this makes sense and helps!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you, Genevieve,
This worked perfectly.
-
No problem! 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!