Excel Conditional Formatting - 'IF' Formula Syntax

Question

I would like to use this formula as a conditional formatting formula:

=IF(COUNTIFS(AccountsMonthsOrdered[Account],'Account Summary 2013'!$A$1,AccountsMonthsOrdered[Month],'Account Summary 2013'!F$12)>0,1,0)

It checks to see if the account name $A$1 and month F$12 appear together in the AccountsMonthsOrdered table, returning 1 if it does, and 0 if it does.

It works fine when it's in a cell on its own, but no matter how I try to rephrase it Excel won't accept it in the conditional formatting formula box.

in CF you don't really need an IF function, just the test, so instead of =IF(test,1,0) you can use simply =test - other than that K_B's answer should do what you want - which version of Excel are you using....because in some versions (2007 for example) you can't refer to other sheets in CF either..... — barry houdini
– barry houdini, Commented Mar 20, 2013 at 16:28
I ended up using the formula =COUNTIFS(AccountRange,$A$1,MonthRange,F$12)=0 and that seems to do the job. Excel 2007. — Wilskt
– Wilskt, Commented Mar 20, 2013 at 16:34

K_B · Accepted Answer · 2013-03-20 15:48:20Z

2

The formula in conditional formatting does not accept Table columns... To remedy make a named range that points to the [Account] column from your table, reference the named range in your formula instead of to the table column. Same for [Month]

answered Mar 20, 2013 at 15:48

K_B

3,6781 gold badge21 silver badges29 bronze badges

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

1 Comment

Wilskt Over a year ago

Switched to named ranges and some other tweaking and it seems to work so thanks. :)

Collectives™ on Stack Overflow

Excel Conditional Formatting - 'IF' Formula Syntax

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related