Test for blanks in range

  • Thread starter Thread starter Alexia
  • Start date Start date
A

Alexia

I want to use ISBLANK(xxxx) to test if the value is blank,
but I need to test 100 cells within the same formula.
However, I get a "formula is too long" error when I have
included only 75 or so of these in the IF statement.

1. Is there a way to test the range for blanks (instead of
one cell)?

2. Is there a way to write the IF statement to test the
range of 100+ cells for blanks?

Thanks!
 
Alexia, if you want to test the whole range to see if it is all blank or not
use this =ISBLANK(A1:A100)

--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
Aleixa,

If you array formula you should be able to do it, for instance
=IF(ISBLANK(H1:H100),"Y","N")

as it is an array formula, use Ctrl-Shift-Enter to enter the formula, and
you should see it surrounded by braces in the formula bar.
 
=1-ISNA(MATCH(TRUE,INDEX(ISBLANK(A1:A10),0,1),0))

with as result 1 (there is at least an empty cell in A1:A10) or 0 (no empty
cells).

=SUMPRODUCT(ISBLANK(A1:A10)+0)

would count empty cells.

=COUNTBLANK(A1:A10)

would count empty cells as well as cells housing a formula-blank (i.e., "",
generated by a formula).
 
=1-ISNA(MATCH(TRUE,INDEX(ISBLANK(A1:A10),0,1),0))

with as result 1 (there is at least an empty cell in A1:A10) or 0 (no empty
cells).
Click to expand...

Yipes! 3 levels of nested function calls!
=SUMPRODUCT(ISBLANK(A1:A10)+0)

would count empty cells.

=COUNTBLANK(A1:A10)

would count empty cells as well as cells housing a formula-blank (i.e., "",
generated by a formula).
Click to expand...
...

Or

=SUMPRODUCT((X=0)*(X=""))

to count truly blank cells, and

=SUMPRODUCT((X<>0)*(X=""))

to count only zero-length strings. Trick here: only truly blank cells return
True for both X=0 and X="".
 
Back
Top