Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Ignoring N/A Values in a Sum.
Written by Allen Wyatt (last updated June 6, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
Chris has a series of worksheets in a workbook, one for each month of the year. On a summary worksheet he wants to sum the values in the same cell on each worksheet. Chris does this by using a formula similar to the following:
=SUM(January:December!B19)
This works fine, except for those instances where one of the B19 cells in the range may contain the value #N/A. In that case, Chris gets #N/A in the result on the summary sheet, as well. What Chris would like is to have the #N/A results ignored for the sum, as if the cells were blank.
There are a couple of ways to approach this problem. Perhaps the best method is to look at the formula used in cell B19 of each month's worksheet. For instance, let's say that the formula on each worksheet looked like this:
=SUM(B1:B18)
You could change the formulas on these individual worksheets so that they took the possibility of #N/A values into account. For instance, the following would work just fine at B19 on each worksheet:
=SUMIF(B1: B18,"<>#N/A")
This causes the sum in cell B19, on each worksheet, to be based on all the non-N/A values in the range. Because of this, you might think you could do this on the summary sheet:
=SUMIF(January:December!B19,"<>#N/A")
This won't work, however, because the SUMIF function is not "three-dimensional" in nature; it cannot be used on a range of worksheets in the manner shown. It is for this reason that the best solution is to go back to the individual values, on each worksheet, that are being tallied on the summary worksheet.
If your formula on the individual month worksheets don't use the SUM function, it is obviously not as easy to change them to use SUMIF. In that case, you may want to "enclose" the existing formula in a check to see if the formula returns an error value. This technique is done this way:
=IFERROR(<current_B19_formula>,0)
The IFERROR function simply checks to see if there is an error returned by the formula. If there is, then it returns 0; if there isn't, then it returns the result of the formula.
There is a big difference between the IFERROR approach and using the SUMIF approach mentioned earlier. The SUMIF approach returns a sum for all non-N/A values in the range, but the IFERROR approach returns a 0 for the entire sum if there are any #N/A values in the range. This can obviously affect what shows up on your summary sheet, so you will need to determine which approach is best suited to the data you are working with.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10233) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Ignoring N/A Values in a Sum.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
If you want to find out the Zodiac sign for a birthdate, there are a number of ways you can do it. This tip provides ...
Discover MoreDo you want to insert the name of your Excel workbook into a cell? There are a number of ways you can do this, as ...
Discover MoreIf you have a list of names in a column, and you want to separate those names into individual cells, there are several ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-06-13 17:16:33
John Mann
I'm inclined to wonder why one wouldn't want to find the cause of an error and correct it, rather than ignoring it.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments