Need a formula to flag a project that is red 2 months in a row
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
Best Answer
-
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)
Answers
-
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.
-
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?
-
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)
-
YESSSSSS! That works perfectly!!
Thank you!
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!