Excel Video 365 has a clever way to sum a range of data even if the data has errors in it. You may have noticed that if your data has an error message in it, Excel formulas like SUM show the error instead of the total. My first choice is always to fix the error, but if you can’t fix the error for whatever reason, you can use an array formula to ignore the error messages and get a sum.
Watch for how the ISERROR function works. That’s the key to making our array formula work. ISERROR is true if there’s an error and false if there’s not an error. Let me be clear. By error I mean things like dividing by zero or looking up a value that isn’t there. Excel can’t give you an error message if you type the wrong number or you reference the wrong cell. By using SUM and IF with ISERROR, we can change all of the error values to an empty string “” and just sum the remaining values. It’s a clever trick. We’ll take it a step farther in the next Excel Video.
