"Review status" based on multiple checkboxes
Hello!
I need help trying to figure out formulas that triggers the review status to green or red based on how many checkboxes that represent each month of the year are checked off. The boxes that are checked off would be based off on the frequency column which is semiannually, annually, biweekly, daily, quarterly, weekly, and monthly. However, the formula needs to be catered to the "Controller". So far, I have tried to use the Countif formula in the review status, but it is counting all checkboxes, rather than the checkboxes that are assigned to the controller.
I did figure out the monthly formula which is:
=IF(AND(JAN@row = 1, FEB@row = 1, MAR@row = 1, APR@row = 1, MAY@row = 1, JUNE@row = 1, JULY@row = 1, AUG@row = 1, SEP@row = 1, OCT@row = 1, NOV@row = 1, DEC@row = 1), "Green", "Red")
Thank you in advance for you help!
Best Answer

My suggestion would be to use an IF/AND/COUNTIFS combo for all of them then.
COUNTIFS(Jan@row:Dec@row, @cell = 1)
The above will tell you how many boxes are checked. You can combine this in an IF statement with an AND function to say that IF the Frequency@row = "x" AND the COUNTIFS() = #, "Green".
=IF(AND(Frequency@row = "Monthly", COUNTIFS(Jan@row:Dec@row, @cell = 1) = 12), "Green", "Red")
Then we can write out multiple AND functions using the same logic (frequency and count) and combine them with an OR function.
=IF(OR(AND(.....), AND(.....), AND(.....)), "Green", "Red")
You should be able to use copy/paste to maintain consistency in your AND functions and just update the actual frequency and required count for each.
Answers

Exactly how are you determining how many boxes should be checked off for each? I can understand monthly would be all of them, but for the others that are greater than monthly, is it just an overall count of how many boxes are checked regardless of which month(s), or does it need to be specific months? For the ones that are less than monthly, how do you track that?

Hi! I figured out after I posted my initial question is that the formula that I used for monthly would be the same formula to track weekly and daily. However, for bimonthly (6), quarterly (4), semiannually (2), and annually (1) would be an overall count of how many boxes are checked regardless of which month it is.

My suggestion would be to use an IF/AND/COUNTIFS combo for all of them then.
COUNTIFS(Jan@row:Dec@row, @cell = 1)
The above will tell you how many boxes are checked. You can combine this in an IF statement with an AND function to say that IF the Frequency@row = "x" AND the COUNTIFS() = #, "Green".
=IF(AND(Frequency@row = "Monthly", COUNTIFS(Jan@row:Dec@row, @cell = 1) = 12), "Green", "Red")
Then we can write out multiple AND functions using the same logic (frequency and count) and combine them with an OR function.
=IF(OR(AND(.....), AND(.....), AND(.....)), "Green", "Red")
You should be able to use copy/paste to maintain consistency in your AND functions and just update the actual frequency and required count for each.

It worked! THANK YOU SO MUCH!!

Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 123 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!