"Review status" based on multiple checkboxes


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 semi-annually, annually, bi-weekly, 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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!