Sum Values in a Range While Ignoring #N/A

Excel provides an easy way to sum a range of cells based on a given criteria.  There are many ways this can be used but the example we are going to look at today is summing a range while ignoring #N/A’s in the same range.  Without ignoring #N/A, the result of the sum will be #N/A itself.  Let’s take a look at how we can use Sumif to make this work.

[ad#in-post-ad]
Example
For this example, let’s take a range that has both #N/A values and numeric values.  Using the following formula, we can exclude all #N/A’s from being included in the sum:

=sumif(A1:A10,”<>#N/A”)

This formula will sum all values in the first column between rows 1-10 that does not contain #N/A.  There are many other instances where the sumif formula can be used that makes summing large ranges possible.  We will explore these as well as the CTRL+SHIFT+ENTER formulas, that provide even more functionality. 

More Excel Tips

• Sumif and Countif Formulas in Excel
• Check if a Cell Contains Text
• Summing Across Multiple Sheets
• Find the 2nd Largest Value in a Range
• Quick Way to Check for Duplicates in a Column