Conditional format if formula result is empty string

[ollyhughes1982]

Hi,

Re the image enclosed, I am trying to use the following formula to conditionally highlight cells when the result of the formula in the cell is not an empty string.

=COUNTIF($EQ$3:$EQ$10002,<>"")

For some reason it doesn't work. I wonder what is it that I am doing wrong, please?

Thanks in advance!

Olly.

 

[Scott Huish]

The formula needs to be in the cell
then you can use a Conditional Formatting rule like this:

 

[ollyhughes1982]

The formula needs to be in the cell
then you can use a Conditional Formatting rule like this:

View attachment 115926

I have formulas in the cells. I have a very similar conditional format where i look for specific text inside “**” and that works. I just thought if I did not equal to empty then it would highlight any cell that has a string in it

 

[ollyhughes1982]

Just to be clear, I am trying to highlight cell EQ2 if the range EQ3:EQ10002 contains any cell that has a text string in it (not truly empty cells, as they all have formulas in - cells where the formula result is an empty cell). i.e. Highlight the header cell if there are any records in the range below that need attention. It's a quick warning, so that I don't have to filter or look down the column to see if there are any entries

 

[ollyhughes1982]

I have now tried this:

=COUNTIF($EQ$3:$EQ$10002,"<>")

I thought this would work, but it treats cells with formulas as not empty and highlights EQ2, despite there being no text strings in the referenced range. Is there a way to get it work so that it only highlights if the cell doesn't have a string in any cells?

 

[Fluff]

For text use "?*" rather than "<>"

 

[ollyhughes1982]

Thanks. After more research online, I have now come up with the following:

=SUMPRODUCT(LEN($EQ$3:$EQ$10002))>0

It seems to work. Basically a bit of a workaround, by counting the total number of characters in the range: if the total is more than 0 then highlight EQ2

 

[Fluff]

Did you try what I suggested?

 

[ollyhughes1982]

Did you try what I suggested?

Yes, that does work as well, but only for text. I will be using this formula for more columns, some of which only have numbers (e.g. Lat / Lon) fields, so the LEN method will work in either case.