CODEWITHSUNDEEP
excel-formula
10101
10101

Reputation: 2402

If any cell in range is not "" (empty)

What is the formula to display value from translation worksheet is any cell in range is not blank?

I have range B24:B254 where cells return ="". I need formula to display X if any of cells in range B24:B254 is not =""

I have tried this one but it does not work:

=IF(COUNTA(B24:B254)="";"";"X")

Upvotes: 0

Views: 47

Answers (1)

Chronocidal
Chronocidal

Reputation: 7951

A bit convoluted, but try this:

COUNTA(B24:B254)-(COUNTBLANK(B24:B254)-COUNTIF(B24:B254,"<>*"))

COUNTA(B24:B254)
How many cells contain Values or Formula. This will include =""

 

COUNTBLANK(B24:B254)
How many cells contain Blank Values. This will include =""

 

COUNTIF(B24:B254,"<>*")
How many cells contain No Values or Formula. This will not include =""

Now, this means that (COUNTBLANK(B24:B254)-COUNTIF(B24:B254,"<>*")) is "Cells with Blank Values" minus "Cells without values or Formula", which gives you how many ="" cells there are.

You then subtract this from the "Cells with Values and Formula" number (COUNTA(B24:B254)) to get "Cells with non-blank Values and Formula" (i.e. Exclude ="" from COUNTA)

Upvotes: 1

Related Questions