"Review status" based on multiple checkboxes

Options
✭✭

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 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!

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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?

• ✭✭
edited 07/28/23
Options

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 bi-monthly (6), quarterly (4), semi-annually (2), and annually (1) would be an overall count of how many boxes are checked regardless of which month it is.

• ✭✭✭✭✭✭
Options

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.

• ✭✭
Options

It worked! THANK YOU SO MUCH!!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!