Consecutive Issue Formula
Hello,
I am trying to figure out if there is a formula that will count the number of entries that have occurred since a different entry was input. In this scenario, Yes and No are the only inputs. The Yes and No correlate to whether an item is working properly or not. So the formula I need would populate with the number of entries that have occurred since the last Yes was input.
In the example below, I would need a formula to create a 4 BUT then the number should also reset to 0 if a Yes is input. I am assuming that I need to use some type of logic formula for this.
The cherry on top (but this is only a nice to have) would be if I can also attach a date formula to this so that it only updates the formula once per day. In this sheet there are sometimes 2-3 entries per day. But if this aspect is too complicated, I am absolutely fine with just the above formula.
Answers
-
Hi @CRUSJ
Where are you looking to put this formula, is it in the Grid of the same sheet, a Sheet Summary field, or in a second sheet?
If you're in the same sheet, you could use a formula to COUNT how many rows for this specific Item have had "NO", and then use a MAX(COLLECT to find the most recent "Yes" row and only count rows created after that date.
To do this, you'd need a System Created Date column, and also a unique identifier column for the Items:
=COUNTIFS([Item name]:[Item name], "Item 2", [Consecutive NO Formula]:[Consecutive NO Formula], "NO", Created:Created, >MAX(COLLECT(Created:Created, [Item name]:[Item name], "Item 2", [Consecutive NO Formula]:[Consecutive NO Formula], "YES")))
You can either type in the Item you're searching for, e.g "Item 2", or reference a cell in the sheet or in the Sheet Summary field.
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. Thanks for your reply!
This would most likely be within a sheet summary so that it could be then correspond to a dashboard. Based on how you have your sheet setup, it seems like this would work. I will incorporate this into my sheet and then follow up with you after the holiday.
Thank you so much for your help! This has been a complex formula that I have been racking my brain trying to figure out.
-
No problem, @CRUSJ!
This should work just fine in a Sheet Summary field:
Hope you have a good holiday, and chat soon. 🙂
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi @Genevieve P. ,
I started working on this but am having trouble getting my sheet data put into it properly. I am trying to put it directly into the sheet just to ensure it works properly before I put it into the sheet summary. Below is the screenshot of the sheet.
-
Hi @CRUSJ
It looks like your data is formatted differently in this screen capture. Instead of looking for an exact match for "NO" or "YES", instead you'll want to see if the cell contains the word "No" or the word "Yes", does that make sense?
=COUNTIFS([Item name]:[Item name], "Item 2", [Consecutive NO Formula]:[Consecutive NO Formula], CONTAINS("No" @cell), Created:Created, >MAX(COLLECT(Created:Created, [Item name]:[Item name], "Item 2", [Consecutive NO Formula]:[Consecutive NO Formula], CONTAINS("Yes", @cell))))
Of course, adjusting the column names as well.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!