Error in conditional formatting formula where the same formula works in a cell

Question

I'm trying to conditionally format a column of cells based on whether the combination of two other columns appear in a Table.

Here is a link to the test workbook I am playing with and screenshots below for those that don't like clicking strangers links!

https://1drv.ms/x/s!Al1Kq21dFT1ij4ktFd0mzBniNX00tQ?e=L6aQm4

On the far left is an Excel table ([Table1]) that contains a list of valid combinations of [Category] and [Item]

• Columns E&F contain some sample data to test against
• Column G is the number of matching combinations I expect to return from a COUNTIFS() function
• Column H is simply the same formula compared to 0 so I get a boolean result.
• The actual formula to get the result shown in Column H is =COUNTIFS(Table1[Category],"="&E4, Table1[Item],"="&F4)=0

All the above works as expected.

In Column J is just some literal text with conditional formatting. The condition is simply =H4, again this works as expected.

Now to the problem... I want to avoid having the helper column (H) so I thought I could just use the same formula that I used in column H, as my condition formula.

So, I tried to use this in the conditional formatting formula dialog.

=COUNTIFS(Table1[Category],"="&E4, Table1[Item],"="&F4)=0

and with parantheses

=(COUNTIFS(Table1[Category],"="&E4, Table1[Item],"="&F4)=0)

Unfortunately, this results in the generic "There's a problem with this formula" error message.

If might be that there are some limitations with conditional formatting formulae that I'm not aware of (I'm no Excel guru, I'm a SQL developer really).

BTW: I need to stick with using a table as my real-world scenario is that there will be several tables, all populated from a database via a separate process with lengths varying from 2 or 3 entries to potentially thousands.

I would appreciate any help, even if it's just to say "You can't do this, you'll need to use your helper column..."

Thanks for looking...