Excel Techniques to Sum Data with Errors
There are times that you need to know the sum of numeric data even though there may be errors in it. The most common of these error values are #DIV/0!, #NUM!, #N/A and #VALUE!. #DIV/0! refers to a situation where Excel is trying to divide by zero or by a cell that is blank. This is mathematically impossible, hence the error value. The #NUM! error value refers to a situation where there is a problem with the underlying formula and the #VALUE! error value most often occurs when Excel is trying to perform a mathematical operation with text.
There are numerous functions and techniques in Excel to sum data with errors. Some have been recently developed by Microsoft and are much easier to use than the older techniques. We’re going to start with the oldest technique first, then progress to the newer, easier techniques so that we cover all the versions of Excel.
Oldest Technique – ISERROR
The ISERROR function in Excel evaluates a cell and returns TRUE if there is an error and FALSE if there is not an error. Because you only have a TRUE value or a FALSE value to work with, this function must generally be paired with an IF function to be useful. The IF function in Excel is structured so that there is a value if TRUE and a value if FALSE.
Here’s an example: More…..