Consecutive Issue Formula

CRUSJ

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.

Genevieve P.

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

CRUSJ

@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.

Genevieve P.

No problem, @CRUSJ!

This should work just fine in a Sheet Summary field:

Hope you have a good holiday, and chat soon. 🙂

Genevieve

CRUSJ

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.

Genevieve P.

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