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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 2 years agoIs this apply if I replace "=SUM" with "=Average"??