How to sum values without or with n/a in Excel?
When summing a range of data mixed with #n/a error, you will get the #n/a error result instead of the correct result. For getting the accurate calculate result, you need to exclude the #n/a cell, or just include the #n/a cell in the formula. With this article, you will learn how to sum values without or with n/a in Excel.
|Replace #N/A or all error values with zero, custom message or cell reference|
Kutools for Excel's Error Condition Wizard utility can quickly add error condition to a formula or group of formulas to display the formulas that produce errors with the 0(zero), a custom message, blank cells, cell reference, etc.
Excel Productivity Tools
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
In this section, we will show you two formulas of summing values without or with n/a in Excel. Supposing you have a range of data as below screenshot shown. For summing all values without or with n/a, please do as follows.
1. Select a blank cell, copy and paste the one of the below formulas into the Formula Bar:Formulas:
A: Sum without #n/a: =SUMIF(A2:C6,"<>#n/a") + Enter key
B: Sum with #n/a: =SUM(IF(ISNA(A2:C6),0,A2:C6)) + Ctrl + Shift + Enter keys together.
1. A2:C6 is the range you will sum. You can change it as you need.
2. When using the first formula, all n/a cells in the range won’t be included in the summing, while using the second one, all n/a cells are included in the summing. But these two formulas will get the same summing result.
You can see the summing result as below screenshot shown.