# Looking for a formula that will auto increment number every day based on other cells assigned values

Options
✭✭

Essentially I am looking to count days and issue is open and assigned to our company.

I was able to somewhat achieve this using a second sheet but there is an administration overhead to this. Issues were copied to second sheet every night and then column in main sheet incremented the days with Team column based on current assigned Team and Status

IE

=IF([Assigned Company]@row = "Our Company", IF([Summary Status]@row = "Open", 1, 0), 0) + +IFERROR(INDEX({OldIssues-DaysPP}, MATCH([Ref #]@row, {OldIssues-REF}, 0)), 0)

Any ideas on how to achieve in a single sheet?

• ✭✭
Options

Also Tried this in single sheet

IF(AND([Assigned Company]@row = "Our Company", [Summary Status]@row = "Open"), [Days with PP]@row + 1, [Days with PP]@row)

1. this returns a circular Reference error
2. I think it would just keep incrementing as no data reference

• Options

Yes, I am calculating the age of something based on a Purchase Date. The first is Today's Date using the formula

The second is Age using the formula

• Options

The result is

• ✭✭
Options

Hey Tammy, Unfortunately I already have a days open option and need to only count days if Status is Open and Company Assigned = Our Company

• ✭✭✭✭✭✭
Options

Are you able to provide a screenshot for reference (mocked up data is fine / just need to see overall structure and some sample data).

• ✭✭
Options

Essentially 3 of the above columns are what needs to be involved.

If Summary status = "Open" AND Assigned Company = "Pinpoint" then increment Days with PP on every day.

Essentially tracking time that an open issue stays with my team vs client.

• ✭✭✭✭✭✭
Options

Hi @RHOFF

I hope you're well and safe!

Two options come to mind.

• Workflow combined with the change a cell action and multiple condition paths referencing the current number. (If it's 0, change to 1, if it's 1, change to 2...)
• Workflow combined with the copy row action that would copy the row each time the criteria is true, and then you'd add a formula that counts how many rows that match in the so-called helper sheet.

Would any of those options work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

You would need to somehow log the date that you want the counter to start and stop at. If you are not doing this manually, you can set up a Record A Date automation.

Then you would use a formula such as this to calculate the number of days:

=NETWORKDAYS([Start Date]@row, IF([End Date]@row <> "", [End Date]@row, TODAY()))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!