Ignoring null cells.

[Ken]

We have a workbook with several hundred rows in four
columns. We imported data into the workbook. Some of the
cells have data, some have none. When we try to enter a
formula to sum columns B, C, and D, we get a "#VALUE!"
error in those rows where at least one of the cells is
blank. We assume that is because some kind of null value
is in the blank cell. If we Edit - Clear - All on the
blank cells, the formula works.

Is there a way to amend the Sum formula to ignore those
blank (null) cells?

=Sum(B11)

TIA.

 

[Frank Kabel]

Hi
SUM(B11)
should ignore blank cells and cells with text values. Is this the exact
formula you use?

 

[Dave R.]

=SUM(IF(ISNUMBER(B11),B11))

this will do the trick. must be entered with CTRL SHIFT ENTER, will appear
automatically as

{=SUM(IF(ISNUMBER(B11),B11))}

if entered properly.

 

[Ken]

Sorry, I mis-spoke (mis-wrote).
The formula actually looks at every other column.
=B1+D1+E1

 

[Ken]

Please my response to Frank's post.

-----Original Message-----
=SUM(IF(ISNUMBER(B11),B11))

this will do the trick. must be entered with CTRL SHIFT ENTER, will appear
automatically as

{=SUM(IF(ISNUMBER(B11),B11))}

if entered properly.





.

 

[Frank Kabel]

Hi
try
=SUM(B1,D1,E1)

 

[Dave R.]

=SUM(IF(ISNUMBER(B1:E1),B1:E1))-IF(ISNUMBER(C1),C1,0)
entered with CSE.

 

[Ken]

Perfect.
Thanks.

-----Original Message-----
Hi
try
=SUM(B1,D1,E1)

--
Regards
Frank Kabel
Frankfurt, Germany


.