CODEWITHSUNDEEP
excelformulaconditional-formatting
Matt Bartlett
Matt Bartlett

Reputation: 360

Formula will not work in Conditional Formatting

I have a three page work book. Pages 2 and 3 hold similar data but from different time periods (Cases Current, Cases Historic). Takes together the information from Cases Current and collates certain information using COUNTIF.

I have been trying to get a conditional format to change the text colour if the COUNTIF from Cases Current is higher or lower than a COUNTIF on the same data in the historic page. E.g.:

Cell C3 has the following formula : '=COUNTIFS('Cases '!$D:$D,C$2,'Cases '!$C:$C,$A3) and displays the figure 7.

I have used a IF formula in a regular cell and this following does work. (In this example the COUNTIF returns 10.)

IF(C3<COUNTIFS(Compare!$D:$D,C2,Compare!$C:$C,$A3),"Lower","Higher")

I have used the same formula in Conditional Formatting but it triggers no change in font colour. I have stripped the two end conditions off the end of the IF statement as the conditional formatting is triggered on a TRUE return.

=IF(C3<COUNTIFS(Compare!$D:$D,C2,Compare!$C:$C,$A3)

Upvotes: 0

Views: 533

Answers (1)

Grade &#39;Eh&#39; Bacon
Grade &#39;Eh&#39; Bacon

Reputation: 3823

This formula is incorrect for what you're attempting:

=IF(C3<COUNTIFS(Compare!$D:$D,C2,Compare!$C:$C,$A3)

It is actually an incomplete IF statement - try to copy it into a cell, and you will see that you don't have an argument for IF's "TRUE" state (which is required). Instead, you don't need an IF statement at all, just:

=C3<COUNTIFS(Compare!$D:$D,C2,Compare!$C:$C,$A3)

So this will check the value of cell C3, then check the value of the COUNTIFS function, and then check that C3 < COUNTIFS. If it is, it's a true statement, resulting in TRUE. Otherwise, it returns FALSE.

Upvotes: 1

Related Questions