CODEWITHSUNDEEP
excelexcel-formulaconditional-formattingcountif
Christopher Birk
Christopher Birk

Reputation: 27

CountIF to Count Cells Affected by Conditional Formatting

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".

enter image description here

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

Upvotes: 1

Views: 2295

Answers (2)

Christopher Birk
Christopher Birk

Reputation: 27

I figured out my own formula using the table headers and a combination of SUM( COUNTIFS( COUNTBLANK(. It's battle tested and works!

=SUM(COUNTIFS(TT[Fiscal Law 301 CBT],"<"&TODAY()-1095,TT[Fiscal Law In-Residence],"<"&TODAY()-1095),COUNTBLANK(TT[Fiscal Law 301 CBT]),COUNTIFS(TT[Fiscal Law In-Residence],"",TT[Fiscal Law 301 CBT],"<"&TODAY()-1095))

Upvotes: 0

Chronocidal
Chronocidal

Reputation: 7951

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 30th 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)

Upvotes: 1

Related Questions