Need a formula to flag a project that is red 2 months in a row

Tina Ciak

Hello,

I have a sheet setup where my team has all of our goals listed for the year.

For each goal I have a column for each month, where we use symbols to mark the project red, yellow, green or gray (if not started) at the end of each month for our report out. This year I was asked to add a column to identify the projects that have been red for 2 or more consecutive months.

Can anyone help with the formula to make this happen?

TIA!

Tina

Leibel S

I see the issue, you add all the months for the current year... the formula only looks at your last 2 columns.

Do you always fill out a status?

The below might work for you:

=IFERROR(IF(AND(INDEX(COLLECT(Jan@row:Dec@row, Jan@row:Dec@row, @cell <> ""), COUNT(Jan@row:Dec@row)) = "Red", INDEX(COLLECT(Jan@row:Dec@row, Jan@row:Dec@row, @cell <> ""), COUNT(Jan@row:Dec@row) - 1) = "Red"), 1, 0), 0)

Leibel S

@Tina@Masco

A couple steps here:

Create a column at the end of your sheet (after the last month column) let's call it 'BLANK'. You wont be filing out anything here at any time

Create a column after that one call it whatever you wish (2 month red flag). Formula would be:

=IF(AND(INDEX([month #1]@row:BLANK@row, 1, COUNTIF([month #1]@row:BLANK@row, OR(@cell = "", @cell <> "")) - 1) = "Red", INDEX([month #1]@row:BLANK@row, 1, COUNTIF([month #1]@row:BLANK@row, OR(@cell = "", @cell <> "")) - 2) = "Red"), 1, 0)

Any subsequent columns should be added to the left of the BLANK column.

Change the name of the month #1 column in my formula.

Tina Ciak

@Leibel S

Thanks for the quick response. I just tried this formula, but it doesn't appear to be working. I'm not getting an error, but it's also not flagging when I add reds to the months.

One Question, Why do I need a Blank column? Can I just make it go from Jan@row:Dec@row?

Leibel S

I see the issue, you add all the months for the current year... the formula only looks at your last 2 columns.

Do you always fill out a status?

The below might work for you:

=IFERROR(IF(AND(INDEX(COLLECT(Jan@row:Dec@row, Jan@row:Dec@row, @cell <> ""), COUNT(Jan@row:Dec@row)) = "Red", INDEX(COLLECT(Jan@row:Dec@row, Jan@row:Dec@row, @cell <> ""), COUNT(Jan@row:Dec@row) - 1) = "Red"), 1, 0), 0)

Tina Ciak

YESSSSSS! That works perfectly!!

Thank you!