Updating RYGB status based on date AND dropdown selection in multiple columns

Paul Newcome

To help avoid having to adjust formulas or miscounting columns etc if one is added or removed, you could use a COUNT function either in a helper column or in the formula itself to automatically count the number of columns for comparison. As long as new columns are added and/or old columns are deleted from in between the the two columns referenced in the formula, it will automatically compare to the correct number. Something along the lines of...

 

=COUNTIFS([First Column Name]@row:[Last Column Name]@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell))))

 

This will give you a count of all cells that are both blank and not blank within the specified range. So in this particular case...

.

In a helper column:

=IF(COUNTIF(Barry@row:Dianne@row, OR(@cell = "Done", @cell = "N/A")) = [Count Helper]@row, "Green", IF([Due Date]1 < TODAY(), "Red", IF([Due Date]1 = TODAY(), "Yellow", "Blue"))

.

In the formula itself:

=IF(COUNTIF(Barry@row:Dianne@row, OR(@cell = "Done", @cell = "N/A")) = COUNTIFS(Barry@row:Dianne@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell)))), OR(ISBLANK(@cell), NOT(ISBLANK(@cell)))), "Green", IF([Due Date]1 < TODAY(), "Red", IF([Due Date]1 = TODAY(), "Yellow", "Blue"))