Ignoring Blank cells in a formula to avoid #value! error

[whitby01]

I have a formula to add several cells then multiply by another cell. However, any of the cells may be blank and I don't want to fill with 0.


=(D26+E26+F26+G26+H26) * I26

Whenever D26 to H26 has a blank I get the #value! error.

 

[Brian from Maui]

I have a formula to add several cells then multiply by another cell. However, any of the cells may be blank and I don't want to fill with 0.


=(D26+E26+F26+G26+H26) * I26

Whenever D26 to H26 has a blank I get the #value! error.

There are a few ways.

One is to go to Tools/Options/View, uncheck zero values

another way would be to use an IF formula

 

[MMGroup]

Avoiding the error

Two other thoughts: you seem to be adding together data in a row. If you use the sum() function, you will get no error. Because you will get an error if you changed the multiply to a divide and all the values are blank, I'd recommend this general purpose solution:
=if(sum(d26:h26)=0,"",sum(d26:h26)*i26)
This says: if the sum of all the fields is 0 (they are all blank or zero), leave the cell blank, otherwise, multiply the sum of the cells by i26. To consider: if zero is a valid response different from blank, this needs to be done differently (this is the case for some statistical functions).

 

[just_jon]

Not sure, but I think the SUM function ignores blanks, so --

=SUM(D26:H26) * I26

should fix the problem.

 

[Barry Katcher]

Hi, whitby. Welcome to the Bored.

First of all, using your formula, if you had true blank cells anywhere in the range, it would not return a #VALUE error. If you have what looks like blank cells, they must have had a space or two entered to them. Look at one of them in your formula bar and see if there is an apostrophe showing. A space is considered text and will return an error with your formula. To avoid that, either:

1. Use jon's suggestion of a =SUM() formula, or

2. Get rid of the "blank" space cells and validate your range to just accept numbers.