Increment previous row by 1 using column formula (when condition is met)
Hello Smartsheet Community,
Looking for what should be a simple solution here: ultimately need to increment count by +1 once a new Project name is entered in a new row. If there are 8 Items within a Project, they will carry the same Project Count value, until a new Project is entered, after which Project Count increases it's own prior row value by +1… and so no.
Here is what I have:
where:
Row ID is AutoNum
Row ID2 formula: =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)
Item Count formula: =COUNTIFS(Project:Project, Project@row, [Row ID]:[Row ID], <=[Row ID]@row)
Count Helper formula: =[Project Count]@row (hence circular reference error with next column.. may not even be required!)
Project Count formula: =IFERROR(IF([Item Count]@row = 1, SUMIFS([Count Helper]:[Count Helper], [Row ID2]:[Row ID2], <=[Row ID2]@row) + 1, SUMIFS([Count Helper]:[Count Helper], [Row ID2]:[Row ID2], <=[Row ID2]@row)), 1)
It is Project Count which I want to begin at 1 for rows 1 and 2, and once the Project column changes, that increases to 2 (and remains in rows 3 and 4… if "Redelivery" were to be added again under Project, that new row would remain at 2, until new row Project is changed to a different input value, where it would then become 3… and so on).
Should be possible, I may be overthinking it. Your assistance is very much appreciated!
Best Answer

Found a workaround in part using @DylBlake's comment in this thread (nice workaround!):
https://community.smartsheet.com/discussion/107139/greetingsiamtryingtofindawaytoidentifythefirstoccurrenceofanentryinacolumn
Finally, Project Count sums all values at and above current row from the Check column results.
MATCH1 formula: =MATCH(Project@row, Project:Project, 0)
Check formula: =IF([Row ID2]@row = [MATCH1]@row, 1, "") → thanks @DylBlake for both of these!
Project Count formula: =SUMIFS(Check:Check, [Row ID2]:[Row ID2], @cell <= [Row ID2]@row)
I'm sure this can still be achieved in a much cleaner way and if so please do share! Cheers.
Answers

Found a workaround in part using @DylBlake's comment in this thread (nice workaround!):
https://community.smartsheet.com/discussion/107139/greetingsiamtryingtofindawaytoidentifythefirstoccurrenceofanentryinacolumn
Finally, Project Count sums all values at and above current row from the Check column results.
MATCH1 formula: =MATCH(Project@row, Project:Project, 0)
Check formula: =IF([Row ID2]@row = [MATCH1]@row, 1, "") → thanks @DylBlake for both of these!
Project Count formula: =SUMIFS(Check:Check, [Row ID2]:[Row ID2], @cell <= [Row ID2]@row)
I'm sure this can still be achieved in a much cleaner way and if so please do share! Cheers.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 351 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 443 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!