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).
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.
SUM() is not needed around L1/K5.
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.