isblank() function

[George]

Hi.
I need some help.
I have two columns in a sheet, A1:A100 and B1:B100, in which it is:

The first column A1:A100, have some data (numbers). In the second column
apply B1=A1, B2=A2,……..B100=A100. In the B101 i am trying to apply the
SUM(B1:B100), but only if the cells A1:A100 are not empty (the zero value
must be considered as number, not empty cell). In the case, which the cells
A1:A100 are empty, i would like to not print anything in B101 cell.
I tried the use of ISBLANK() function
{IF(ISBLANK(B1:B100)=FALSE;SUM(B1:B100);"")}, but when the A1:A100 cells are
empty then the B1:B100 column returns zero values and the SUM(B1:B100)
returns 0 instead to remain empty.

Any idea?
Thank you.

n.n.
(In some cases the cells B1:B100 seem to be empty but it still they value
are 0.)

 

[Bob Phillips]

Why don't you check the source data

=IF(COUNTIF((A1:A10),"")=MAX(ROW(A1:A10))-MIN(ROW(A1:A10))+1,"",SUM(B1:B10))

still an array formula

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Mallycat]

I don't really understand what you are trying to do. If
every cell in column B equals the same value in column A, then what is
the point of column B? If you are trying to sum numbers but only if
they are not blank, what is the difference between this and just suming
them. If they are blank they = 0 so no issue. Am I missing
something?

 

[George]

First: The column B is a general case in which receive data from another
part of a sheet, or workbook (here column A) and after performing some
complicated process produce a result. (here is provided a simple example)
Second: Simply blank cell are not equal to zero, so of course blank <> 0.
( www.mrexcel.com/archive/Formulas/31093.html)

Ο χÏήστης "Mallycat" έγγÏαψε:

 

[George]

Yes, I thought about that, but in a complicated general case with many
(input data) workbooks its not so practical. Must be something direct to the
result. Thanks anyway.
Ο χÏήστης "Bob Phillips" έγγÏαψε:

 

[Bob Phillips]

Then why don't you change the formula in B1 to say

=IF(A1="","",A1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[George]

simple and smart. I try it.
Th.

Ο χÏήστης "Bob Phillips" έγγÏαψε: