CountIF to Count Cells Affected by Conditional Formatting

Question

After much research I have found nothing without VBA to count a range of cells that have been affected by conditional formatting (specifically are turned "red").

I know there is no way to count the "red" cells so I am going the route of creating a CountIF formula with the same criteria that is in the conditional formatting but i'm having issues creating the criteria.

I thought it would be simple and to just add "CountIF($G:$G," before the below code. This data is also inside a table named "TT".

=AND(OR(AND(TODAY()-$F1>1095,TODAY()-$G1>1095),$G1=0,AND($F1=0,TODAY()-$G1>1095)),$A1>0)

First step will be to rearrange your formula so that Cells are on one side of comparisons, and everything else is on the other - e.g. Today()-$G1>1095 becomes $G1 < Today()-1095. This way, your COUNTIFS conditions will be in the form $G:$G, "<" & Today()-1095 — Chronocidal
I would insert a helper column in front of after column G to identify the cells that triggered conditional formatting using the same criteria, and do a simple COUNTIF of that helper column. — Terry W

Chronocidal Chronocidal · Accepted Answer · 2019-07-25T15:22:56

The OR makes things slightly mode complicated - you need to add the COUNTIFS, and then subtract when both are true (to prevent double-counting), To demonstrate, if we want where Column A = 0 or Column B = 0:

=COUNTIF(A:A, 0) + COUNTIF(B:B, 0) - COUNTIFS(A:A, 0, B:B, 0)

Except, you seem to be doing this with 3 conditions, which makes it bigger (add individual, subtract where 2 match, then add where all 3 match) - but there's actually a trick here, which I'll get to later.

To make it easier, we can rewrite your conditions from format Value - A1 > Const to A1 < Value - Const. This means the COUNTIF would be Countif(A:A, "<" & Value - Const)

=AND(OR(AND($F1<TODAY()-1095,$G1<TODAY()-1095),$G1=0,AND($F1=0,$G1<TODAY()-1095)),$A1>0)

Now, let's split that out into our individual COUNTIFS. There's the outer AND, so $A1>0 is in all of them, then there's an OR with 3 conditions. This gives us:

COUNTIFS($A:$A,">0", $G:$G, "<" & Today()-1095, $F:$F, "<" & Today()-1095)
COUNTIFS($A:$A,">0", $G:$G, 0)
COUNTIFS($A:$A,">0", $G:$G, "<" & Today()-1095, $F:$F, 0)

Now, here's the trick I mentioned earlier: I don't know about you, but I can see some duplication going on here. For example, the first and the third? Column F is less than Today()-1095, OR Column F is 0. Except, day 1095 is the 30^th December 1902 - so Today()-1095 will always be greater than 0. Today, for example, it will be 42576. This means when the third condition is True, the first condition will also always be true. So, we can ignore the third COUNTIF entirely!

Now, we can't do this with the first and second conditions - because if column F is greater than Today()-1095 the first condition will always be False, but the second condition will be True if Column G is 0

So, using our example from earlier, we have the following:

=COUNTIFS($A:$A,">0", $G:$G, "<" & Today()-1095, $F:$F, "<" & Today()-1095)
+COUNTIFS($A:$A,">0", $G:$G, 0)
-COUNTIFS($A:$A,">0", $G:$G, 0, $G:$G, "<" & Today()-1095, $F:$F, "<" & Today()-1095)

But! Look at that last COUNTIFS. It has G:G = 0 AND G:G < Today()-1095. But, if Column G is 0, then it is also less than Today()-1095 (Disclaimer: On-or-after New Year's Eve 1902) So, we can simplify that:

-COUNTIFS($A:$A,">0", $G:$G, 0, $F:$F, "<" & Today()-1095)

Which means our entire equation is as follows:

=COUNTIFS($A:$A,">0", $G:$G, "<" & Today()-1095, $F:$F, "<" & Today()-1095)+COUNTIFS($A:$A,">0", $G:$G, 0)-COUNTIFS($A:$A,">0", $G:$G, 0, $F:$F, "<" & Today()-1095)

CountIF to Count Cells Affected by Conditional Formatting

2 Answers