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.
Sum values without or with n/a in Excel
Sum values without or with n/a in Excel
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.
Notes:
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.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
