How To Sum a set of Cells Ignoring #N/A Errors In Excel || 3 tricks for the problem|| DataWitzz

How To Sum a set of Cells Ignoring #N/A Errors In Excel

We often encounter an issue where we wanted to sum set cells which already have #N/A in one or more cells. Due to these N/A values sum also reflects N/A instead of a number. How to sum a set of cells ignoring #N/A errors in Excel?

How To Quickly Sum a set of Cells Ignoring #N/A Errors In Excel?
How To Quickly Sum a set of Cells Ignoring #N/A Errors In Excel?

We can see in the above example that there were two entries of #N/A in the dataset due to which #N/A was reflected as a sum value. To encounter these types of problems we would be discussing a few ways forward in this tutorial.

SUMIF function in excel can be used to get rid of this issue. Let’s understand this by the below example.

use of SUMIF in excel
Use of SUMIF in excel

In the above example, we used the SUMIF function of excel. Here we used a condition to sum all the numbers in the dataset except #N/A. So the final result comes as a number.

SUM function with IFERROR can also be used to get value while ignoring #N/A in the data set. Let us check the below example to understand this solution.

SUM function with IFERROR in excel
SUM function with IFERROR in excel

In the above example, we used the SUM function with IFERROR. we used the logic if there is any error then it should be turned to zero and then sum all the numbers. So finally we got a numerical output.

Now the third solution that we will be using is AGGREGATE Function. The Excel AGGREGATE function is a very useful function and it provides to do multiple calculation with multiple exceptions. Let us see the below example of how we used this function for our solution.

Excel AGGREGATE Function
Excel AGGREGATE Function

Here in this example, we used value 9 for SUM and then used 6 to Ignore Error Values. After these values, we used our data set to calculate the sum. Finally, this solution also worked and we got the sum of values.

That is all, for now, will meet again with some new and interesting topic.

You can read more about this from the below article.

https://www.excel-easy.com/examples/sum-range-with-errors.html

Leave a Comment

Scroll to Top