"Review status" based on multiple checkboxes

cware10

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!

Paul Newcome

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.

Paul Newcome

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?

cware10

@Paul Newcome

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.

Paul Newcome

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.

cware10

@Paul Newcome

It worked! THANK YOU SO MUCH!!

Paul Newcome

Happy to help. 👍️