Formula will not work in Conditional Formatting

Question

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)

Grade 'Eh' Bacon · Accepted Answer · 2015-10-22 14:10:35Z

1

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.

answered Oct 22, 2015 at 14:10

Grade 'Eh' Bacon

3,8534 gold badges26 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Formula will not work in Conditional Formatting

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related