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

RHOFF

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?

Thanks in advance

RHOFF

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

Tammy_Bentz

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

Tammy_Bentz

The result is

RHOFF

https://community.smartsheet.com/discussion/comment/353448#Comment_353448

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

Paul Newcome

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

RHOFF

Hi @Paul Newcome

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.

Andrée Starå

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!

Paul Newcome

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()))