Home  ›  Sum Non Continuous Excel Cells Without Adding

Sum Non Continuous Excel Cells Without Adding

Written By Saturday, October 15, 2022 Add Comment Edit

In a large dataset, there is a possibility of having some null or blank cells. SUM function doesn't work with #N/A values but there are several ways to do SUM ignoring #N/A values. In this article, I'm going to explain how to sum ignore #N/A in Excel.

To make it more explainable, I'm going to use a dataset of sales information of individual salesperson of different products. There are 4 columns in the dataset which are Sales Person, Laptop, Iphone and, Ipad. Here these columns represent the sales information of a particular product.

Sample Dataset

Download to Practice

7 Ways to SUM Ignore N/A

1. Using SUMIF

You can use the SUMIF function to ignore #N/A errors.

To use the SUMIF function first, select the cell where you want to place your resultant value.
➤ Here, I've selected the cell F4
Then, type the following formula in the selected cell or into the Formula Bar.

Using SUMIF

Here, selected the cells C4:E4 as range and proved not equal (<>)#N/A as criteria. So, the function will return the sum of the numeric values only.

Finally, press the ENTER key.
Now, it will show the Total Sales of the salesperson Ahmed.

Using SUMIF

Later, you can use the Fill Handle to AutoFill formula for the rest of the cells of the Total Sales column.

Using SUMIF

An Alternate Way

There is an alternate way to use the SUMIF function while ignoring #N/A errors.

For that first, select the cell where you want to place your resultant value.
➤ Here, I selected the cell F4
Then, type the following formula in the selected cell or into the Formula Bar.

Alternate way of SUMIF

Here, the selected range is C4:E4 same as before but I changed the criteria. As criteria, I have used a greater than (>) operator. If the selected values are greater than 0 then SUMIF will sum those values.

Press the ENTER key, eventually, it will show the Total Sales of Ahmed.

Alternate way of SUMIF

Now, you can use the Fill Handle to AutoFill formula for the rest of the cells of the Total Sales column.

Alternate way of SUMIF

Read More: Excel Sum If a Cell Contains Criteria (5 Examples)

2. Using SUM & IFERROR

Here you can use the SUM function and the IFERROR function to ignore #N/A errors.
The SUM function will calculate the sum and IFERROR will ignore the#N/Aerrors (it will ignore any error though).

Firstly, select the cell to place your resultant value.
➤ Here, I selected the F4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Using SUM & IFERROR

Here in the IFERROR function selected cell range C4:E4 as value and given 0 in value_if_error. Now it will pass all the selected values except errors (which it will convert into 0) to the SUM function to calculate the sum.

In the end, press the ENTER key.
Then, it will show the Total Sales of the salesperson Ahmed.

Using SUM & IFERROR

If you want, you can use the Fill Handle to AutoFill formula for the rest of the cells of the Total Sales column.

Using SUM & IFERROR

An Alternate Way

You can use the same formula just by changing the value_if_error.

Here, I used "" as value_if_error. It will give the exact same result as before because this double-quote skips #N/A errors.

Type the following formula in the selected cell or into the Formula Bar.

=SUM(IFERROR(C10:E10,""))

Alternate way of Using SUM & IFERROR

Read More: Sum Formula Shortcuts in Excel (3 Quick Ways)

3. Using SUM & IFNA

You also can use the SUM function and the IFNA function to ignore #N/A errors.
The SUM function will calculate the sum and IFNA will ignore the #N/A errors.

To begin with, select the cell to place your resultant value.
➤ Here, I selected the F4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Using SUM & IFNA

Here in the IFNA function selected cell range C4:E4 as value and given (" ") in value_if_na. Now it will pass all the selected values except #N/A values (rather convert N/A into blank) to the SUM function to calculate the sum.

Now, press the ENTER key.
As a result, it will show the Total Sales of the salesperson Ahmed.

Using SUM & IFNA

Hence, you can use the Fill Handle to AutoFill formula for the rest of the cells of the Total Sales column.

Using SUM & IFNA

Read More: How to Add Specific Cells in Excel (5 Simple Ways)

Similar Readings

  • All the Easy Ways to Add up (Sum) a column in Excel
  • How to Sum Cells with Text and Numbers in Excel (2 Easy Ways)
  • Use VLOOKUP with SUM Function in Excel (6 Methods)
  • Sum Cells in Excel: Continuous, Random, With Criteria, etc.
  • How to Sum Selected Cells in Excel (4 Easy Methods)

4. Using SUM, IF & ISERROR

You can use the SUM function, IF function, and the ISERROR function altogether to ignore #N/A errors.

To use these functions together, select the cell to place your result.
➤ Here, I selected the F4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

=SUM(IF(ISERROR(C4:E4),0,C4:E4))

Using SUM, IF & ISERROR

Here, selected the cell range C4:E4 as the value of the ISERROR function now it will be the logical_test of IF. Then in the IF function provided 0 as value_if_true and the selected cell range as value_if_false.
Now it will check the values and will return the zero for #N/A (or any error) and other non-zero values to the SUM function.

Finally, press the ENTER key.
Therefore, it will show the Total Sales of the salesperson Ahmed.

Using SUM, IF & ISERROR

In short, by using the Fill Handle you can AutoFill formula for the rest of the cells of the Total Sales column.

Using SUM, IF & ISERROR

Read More: How to Add Multiple Cells in Excel (6 Methods)

5. Using SUM, IF & ISNA

You can use the SUM function, IF function and the ISNA function altogether to ignore #N/A errors.

At this time to use these functions together, select the cell to place your result.
➤ Here, I selected the F4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

=SUM(IF(ISNA(C4:E4),0,C4:E4))

Using SUM, IF & ISNA

Here, in the ISNA function selected the cell range C4:E4 as the value, it will work as logical_test to the IF function. Then in the IF function provided 0 as value_if_true and the selected cell range as value_if_false now it will check the values and will return the non-error values (#N/A) to the SUM function.

Press the ENTER key it will show Total Sales of Ahmed.

Using SUM, IF & ISNA

By using the Fill Handle, you can AutoFill formula for the rest of the cells of the Total Sales column.

Using SUM, IF & ISNA

6. Using AGGREGATE

You can use the AGGREGATE function to ignore #N/A errors while using sum.

First, select the cell to place your resultant value.
➤ Here, I selected the F4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Using AGGREGATE

Here in the AGGREGATE function used 9 as function_num (9 means SUM) and 6 as options (6 means ignore error values) then selected the cell range C4:E4 as an array. Now, it will return the sum ignoring #N/A errors.

Finally, press the ENTER key.
Now, it will show the Total Sales of Ahmed.

Using AGGREGATE

Consequently, you can use the Fill Handle to AutoFill formula for the rest of the cells of the Total Sales column.

Using AGGREGATE

Read More: How to Sum Range of Cells in Row Using Excel VBA (6 Easy Methods)

7. Using IFERROR

You also can use the IFERROR function to do the sum while ignoring #N/A errors.

First, select the cell to place your resultant value.
➤ Here, I selected the F4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

=IFERROR(C4, 0) + IFERROR(D4,0)+ IFERROR(E4,0)

Using IFERROR

Here in the IFERROR  function selected the cell C4 as value and 0 as value_if_error. Added the rest of 2 cells using the same IFERROR function.
In C4 and E4 there is no error so the values of these two cells are derived where because of containing #N/A it gave 0 for D4.

Press the ENTER key now it will sum all the selected cells values while ignoring #N/A errors.

Using IFERROR

Later, use the Fill Handle to AutoFill formula for the rest of the cells of the Total Sales column.

Using IFERROR

Read More: [Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)

Practice Section

I've given a practice sheet in the workbook to practice these explained ways to sum ignore #N/A. You can download it from the above.

Practice Sheet

Conclusion

In this article, I tried to explain 7 methods of sum ignore #N/A in Excel. These different ways will help you to perform the sum with multiple #N/A values. Last but not least if you have any kind of suggestions, ideas, and feedback please feel free to comment down below.

Further Readings

  • How to Sum Multiple Rows and Columns in Excel
  • Sum If a Cell Contains Text in Excel (6 Suitable Formulas)
  • How to Sum If Cell Contains Specific Text in Excel (6 Ways)
  • Sum a Column in Excel (6 Methods)
  • How to Add Numbers in Excel (2 Easy Ways)
  • Sum to End of a Column in Excel (8 Handy Methods)

beattiefiefultoothe.blogspot.com

Source: https://www.exceldemy.com/sum-ignore-na-in-excel/

0 Response to "Sum Non Continuous Excel Cells Without Adding"

Post a Comment

Subscribe to: Post Comments (Atom)

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel