Creating a Counter

Josh Clements

Does anyone know of a way to create a counter so everytime a cell becomes a specific work it adds one to the counter. As a coder I would use a loop but I am not sure how to approach this from a formula perspective or if its even possible.

Paul Newcome

What kind of scaling do you need?

Josh Clements

Just up by 1 each time.

Example how many times has this cell become status late for a project

Paul Newcome

Right. Sorry. I meant how often would the cell change to whatever you needed to track and how many rows are in the base sheet?

The basic idea is that you have some kind of unique indicator on every row (or a combination of columns that creates a unique indicator) and then set up a copy row automation to run when that column changes. You would then use a COUNTIFS with cross sheet references to count how many times that indictor (or series of columns generating an indicator) is found on the second sheet.

This works as long as we stay within certain limitations such as sheet size and referenced cells and whatnot, but if you have thousands of rows changing a lot that you would nee recorded, it may not be the solution you are looking for (may need to leverage the API).

Josh Clements

currently the base sheet goes up to 90 rows and growing with upwards of 70 columns it is my large research sheet where we correlate data and then direct from there so adding more columns solely for the purpose of counting is worrying. I know you cant use circular logic in these but i was hoping someone would have a sneaky trick lol

Josh Clements

Oh yes and they potentially change on the daily as the dates in them adjust

Andrée Starå

https://community.smartsheet.com/discussion/109055/creating-a-counter

Hi,

I hope you're well and safe!

How high do you need to counter to be able to go?

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

Ok. So far we aren't in too bad of shape. I understand things can change daily, but how many times in total would be an expected number of changes for a row? I imagine eventually things will start getting closed out?

Josh Clements

Hopefully the counter need go no higher than 5 to ten , but it will be per project per department so potentially max 50

Andrée Starå

https://community.smartsheet.com/discussion/comment/391085#Comment_391085

What do you mean by per project per department? Can you elaborate?

My idea was to use a Workflow with the Change Cell Action, which only makes sense for ten changes per row.

It can handle more, but then it might be better to develop a solution that uses the method Paul mentioned with a helper sheet.

Paul Newcome

Are you able to provide a screenshot of the main sheet (sample data is fine) for context?

Paul Newcome

@Andrée Starå I'm curious how you would use a Change Cell automation for a counter?

Josh Clements

Untitled sheet (1).pdf

Advisor has requested i build a temporary sheet with an example. You will see the behind and on time is run using if statements where it check the finish date against todays date and checks for a completion mark. The goal is if department 1 gets behind its tracked even if we have to adjust the date and is behind again now we know they were behind twice. then in our separate data form i would be summing up per department so we know if we need more training or researchers.

Thanks in advance

Josh Clements

i did look into the automation of changing cell values but it would only let me change it to a fixed value

Paul Newcome

Ok. So are the date columns supposed to be "expected" finish then? What is the formula you have in the column populating "Behind"?

Josh Clements

=IF(AND([Department 1 finish Date]@row < TODAY(), [D1 finished]@row = false), "BEHIND", IF(AND([Department 2 finish date]@row < TODAY(), [D2 finished]@row = false), "BEHIND", IF(AND([Department 3 finish date]@row < TODAY(), [D3 finished]@row = false), "BEHIND", IF(AND([Department 4 finish date]@row < TODAY(), [D4 finished]@row = false), "BEHIND", IF(AND([Department 5 finish date]@row < TODAY(), [D5 finished]@row = false), "BEHIND", "ON")))))