Ask Your Question
0

ignore formula

asked 2019-12-08 04:36:55 +0200

spider gravatar image

In a sum formula such as =sum(m1:m4) in M5, where the intention is to get a total of all entered items in a column - in this case in column M cells m1,m2,m3,m4 how can I change the formula to ignore cells that have no information which show #DIV/0! ? The reason some of the various cells in the column report #DIV/0! is that the formula in those cells is actually =sum(l1/K5).

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2019-12-08 20:48:23 +0200

erAck gravatar image

To ensure that only a #DIV/0! error is ignored and not other errors as well, simply using IFERROR() is not advisable. Instead, test for the error type as well. Unfortunately ERROR.TYPE() and ERRORTYPE() (note these are different functions and ERRORTYPE() returns the internal error number) return #N/A error themself if the argument is not an error value, so something like this is needed (e.g. create an extra column that you then sum):

=IFERROR(M1;IF(ERROR.TYPE(M1)=2;0;M1))

For one simple division you can also directly calculate in column M

=IFERROR(L1/K5;IF(ERROR.TYPE(L1/K5)=2;0;L1/K5))

but note that the expression L1/K5 is calculated up to three times in an error case, hence more complex expressions should be calculated only once in a cell and that cell be referenced.

edit flag offensive delete link more
0

answered 2019-12-08 14:06:19 +0200

gregors15 gravatar image

Hi, try using the function IFERROR, and force 0 as the result of an "ERROR". This is an example =IFERROR(SUM(L1/K5),0) Let us know if it helps.

edit flag offensive delete link more

Comments

SUM() is not needed around L1/K5.

erAck gravatar imageerAck ( 2019-12-08 20:12:43 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-12-08 04:36:55 +0200

Seen: 177 times

Last updated: Dec 08 '19