Increment previous row by 1 using column formula (when condition is met)

mrsci3ntist

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!

mrsci3ntist

Found a workaround in part using @DylBlake's comment in this thread (nice workaround!):

https://community.smartsheet.com/discussion/107139/greetings-i-am-trying-to-find-a-way-to-identify-the-first-occurrence-of-an-entry-in-a-column

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.

mrsci3ntist

Found a workaround in part using @DylBlake's comment in this thread (nice workaround!):

https://community.smartsheet.com/discussion/107139/greetings-i-am-trying-to-find-a-way-to-identify-the-first-occurrence-of-an-entry-in-a-column

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.