Change formula to work in conditional formatting

Hi,

I have the following code that is working ok

Please Login or Register  to view this content.

I need to change so that I can use it in conditional formatting.

I tried to use INDIRECT but I am having some problems because I am not used to work with it. I' ve been thinking to use COUNTIFS instead of SUM.

Any help would be great!

Regards,
Elio Fernandes

I think you need to post a file so we can better understand the context / requirements.

Depending on the XL version being used and the file type used when saved (see * below) the formula you outlined would be viable for Conditional Formatting as formulae are processed as Arrays by default:

Please Login or Register  to view this content.

If you are using XL2007+ you should be using COUNTIFS as it's significantly more efficient.
Moreover, if you are using XL2007 it also circumvents the bug outlined below at foot of post (see * section)

Please Login or Register  to view this content.

* in 2007 there does appear to be a bug in terms of Array formula rules not evaluating correctly on the file being re-opened

Fixes would appear to be any one of:

1) re-apply CF upon open
2) create rules in Compatibility Mode
3) use embedded INDEX calls to remove Array nature of formula

Elio, I can only presume you're (at some point) physically deleting and replacing Table1 else there is no need for INDIRECT
(the INDIRECT doesn't affect the Volatility of the solution here [CF is super volatile regardless] but it's possibly an unnecessary function call)