I think most of us will agree that error values can be helpful, and sometimes extremely unhelpful. For example, the #N/A error (Value Not Available error) is very helpful when cross-referencing two lists using the VLOOKUP function. If Excel can’t find a lookup value the #N/A error makes it very obvious.
However if you are wanting to total a range that includes #N/A errors all of a sudden they aren’t so special any more.
Sum a range of cells that include #N/A or #DIV/0! errors
If you have tried this you will probably be totally frustrated that the SUM function just returns another #N/A value and not the required total. The same happens with the #DIV/0! (Divide by 0 error) as well.
Join the 'Excel at Work Insiders' group today!
100% free. Join now and unlock offers exclusive to this group!
Receive tips and guidance to help you SAVE TIME, WORK SMARTER and IMPROVE YOUR UNDERSTANDING of Microsoft® Office.
In this post I want to give you a couple of options that can be used to get past the error values and give you the range total.
Use this option if you still want to see the errors in the worksheet
Using the SUMIF function you can create a total which excludes the cells containing errors.
This option allows you to still see the #N/A errors in the Total range.
First select the cell that will hold the TOTAL. From the Formulas tab, in the Formulas group click Math & Trig. Select ‘SUMIF’ from the list.
In the ‘Range’ and ‘Sum_range’ boxes enter the range you want to total. In the ‘Criteria’ range enter “<>#N/A” and then click OK.
The arguments you have just entered perform the following tasks:
The ‘Range’ is the range that Excel will evaluate using the ‘Criteria’. In the example below Excel has looked at the range E5:E28 and using the criteria setting of <> (not equal to) #N/A it has then only included in the Total the cells that don’t contain ‘#N/A’.
You can use the same function to total a range with #DIV/0! errors as well.
Use this option to replace the errors with a value
Of course if the errors were replaced with a value the SUM function will perform as normal. Let’s look at how to do this for the #DIV/0! error. You can easily do the same for any error that you need to deal with.
In the example below the SUM function isn’t able to display a total due to the errors in the cells within the range.
However, if I re-write the ‘Unit Cost’ formula to include the IFERROR function I can replace the #DIV/0! errors with a 0 (zero) value. IFERROR checks if the formula you are using will return an error. If it does it will then replace the error with a value.
With all cells in the range now holding a value the SUM function held in C10 has an easy time of creating the range total.
We cover VLOOKUP, SUMIF and IFERROR in our very popular Excel Stage 2 and 3 courses. If you are wanting to learn the features of Excel that save you huge amounts of time these courses are definitely for you!
Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand.
She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.
If you enjoyed this you may also enjoy...
Learn how to use Conditional Formatting to identify cells that are of interest. For example, you can apply a Conditional Format that checks to see a cell’s value is greater than $500. If it is the Conditional Format can change the Fill colour of the cell. Therefore making it really easy for you (and others
Learn how to find, modify and break links to an Excel workbook. Linking is great, until you no longer need it. Then it can be a bit of a pain. Join the ‘Excel at Work Insiders’ group today!100% free. Join now and unlock offers exclusive to this group!Receive tips and guidance to help you SAVE TIME, WORK SMARTER
Learn how to link Link cells in the same or different Excel worksheets. Linking saves a huge amount of time (and a huge amount of mistakes) in that it allows you to create connections from one cell to another. Join the ‘Excel at Work Insiders’ group today!100% free. Join now and unlock offers exclusive to this group!Receive tips
Use the SUMIF function to total only the cells that match your requirement. For example, if you wanted to know the total sales made by one of your sales team members you can use the SUMIF function to only add to the total the sales made by a certain member. Gold! This function saves you a
Learn how to insert an Excel function into your workbook. Excel comes equipped with a large number of pre-defined formulas. These are known as functions. There is a great amount of functions available in Excel. Each created for a specific purpose. There are functions specific to Maths, Science, Finance, and Engineering to name a few. Join the
Learn how to insert subtotal rows into sorted data without having to spend time doing it manually. Recently I ran a training session for an Accounts Manager and her staff. They spent a lot of time pulling data out of their in-house computer system, sorting it by customer and then inserting a new row at