Array function oddity (Excel 2000 SR1)

Topic

New Reply

I just tried to use the array formula:

            {=SUM(IF(A1:A65536="",0,1))}

to count up the non-blank cells in column A.

Excel helpfully translated A1:A65536 to A:A and then gave a #NUM error.

Using

            {=SUM(IF(A1:A65535="",0,1))}

(omitting the final cell from the range) works fine!

Is this a bug or a feature ?

Ian.

Reply | Quote

Replies

Definitely a featbug.

Why not

=65536-COUNTBLANK(A:A)

?

Array formula

{=SUM(NOT(ISBLANK(A:A))*1)}

also works now and for those future Excel versions with 1,048,576 rows.

Reply | Quote

Thanks, John. I didn’t know about COUNTBLANK.

=ROWS(A:A)-COUNTBLANK(A:A)

avoids hardwiring 65536.

Ian.

Reply | Quote

Whoops! How did I forget ROWS() when I was using it in a formula an hour before that post!

Reply | Quote

Of course, if you don’t mind including formulae in column A that return nul results, you could use:
=COUNTA(A:A)
(non-array)

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks, macropod.

It just goes to confirm that there are always 100 different ways to do anything in Office and 95 of them can be found only by asking in Woody’s Lounge!

All the best
Ian.

Reply | Quote