# How to Sum a Range with Errors in Excel

This guide will discuss how to sum a range with errors in Excel using three simple and easy methods.

Excel is an excellent tool containing many different functions and tools. And Excel makes the entire process for us easier with these functions. But, sometimes we commit an error in inputting data while performing calculations.

And Excel immediately shows or displays messages to inform or alert us that there is an error in our calculations or data set. But, this also means we cannot get the correct result or even continue performing the function when an error occurs.

In this case, we will focus on performing a sum in a range in Excel. And there are a few methods we can use to still be able to sum a range with errors in Excel.

Let’s take a sample scenario.

Suppose you are performing calculations in Excel. And you are about to sum a range, but you cannot continue because there is an error within the range. Because you have been doing calculations for a long time, you do not want to go back from the top to fix the error.

Worry not! There are a few functions in Excel we can use to sum a range with errors. But before we learn how to do these three simple and easy methods, let’s first discuss the syntax of these functions.

## The Anatomy of the AGGREGATE Function

The syntax or the way we write the `AGGREGATE` function is as follows:

`=AGGREGATE(function_num,options,array,[k],...)`

Let’s take apart this formula and understand what each term means:

• = the equal sign is how we start any function in Excel.
• AGGREGATE() this is our `AGGREGATE` function. And this function will return an aggregate in a list.
• function_num is a required argument. And this refers to the number 1 to 19 that specifies the summary function for the aggregate.
• options is also a required argument. And this refers to the number 0 to 7 that specifies the values to ignore for the aggregate.
• array is another required argument. And this refers to the array or range containing numerical data on which we will calculate the aggregate.
• k is an optional argument. And this indicates the position in the array, such as k-th largest, k-th smallest, k-th percentile, or k-th quartile.

## The Anatomy of the SUMIF Function

The syntax or the way we write the `SUMIF` function is as follows:

`=SUMIF(range,criteria, [sum_range]) `

Let’s dissect this formula and understand what each term means:

• = the equal sign is how we activate any function in Excel.
• SUMIF() this is our `SUMIF` function. And this function will add the cells specified by a given condition or criteria.
• range is a required argument. And this refers to the range of cells containing the values we want to evaluate
• criteria is also a required argument. And this refers to the criteria or condition in the form of a number, expression, or text which defines the cells added.
• sum_range is an optional argument. So it refers to the actual cells to sum. When we omit this, the function will use the cells in the range.

Awesome! Now that we have dissected some of the functions we will be using, let’s move on and discuss the three simple and easy methods we will use to sum a range with errors in Excel.

## How to Sum a Range with Errors in Excel using the AGGREGATE function

The first method we will try is by using the `AGGREGATE` function. So the `AGGREGATE` function ignores the N/A errors and will simply proceed to get the sum of the range.

To do this method, follow the steps below:

1. Firstly, we must select a cell to input the results. In this case, let’s input the results into the cell. Then, type in an equal sign to start the `AGGREGATE` function. So our entire formula would be “=AGGREGATE(9,6,C2:C7)”. Lastly, press the Enter key to return the results.

2. And tada! We successfully sum a range with errors in Excel using the `AGGREGATE` function.

## How to Sum a Range with Errors in Excel using the SUMIF function

Secondly, we can use the `SUMIF` function to sum a range with errors in Excel. And this is a relatively simple formula and process. But, this method will only work if the errors present in your data set are N/A errors.

So we simply need to use the not equal operator (<>) with the N/A errors to sum the range. If the data set contains N/A errors, the `SUMIF` function will return the sum of values not equal to N/A.

To do this method, let’s learn the steps below:

1. Firstly, we must select a cell to input the results. In the same cell, type in an equal sign and the `SUMIF` function. Then, our entire formula would be “=SUMIF(C2:C7,”<>N/A”)”. Lastly, press the Enter key to return the sum of the range.

2. And tada! We got the sum of a range with errors in Excel using the `SUMIF` function.

## How to Sum a Range with Errors in Excel using the SUM and IFERROR functions

And the last method we will be trying is with the use of the `SUM` function and the `IFERROR` function. So this method will create a more literal array formula with the combination of the two functions.

Basically, the `IFERROR` function traps the errors and converts them to zeros. Then, we can use the `SUM` function as the errors have been converted to zero.

To do this method, let’s follow the step-by-step process below:

1. Firstly, we must select a cell to input the results. In the same cell, input the formula “=SUM(IFERROR(C2:C7,”0″))”. Lastly, press the Enter key to return the results.

2. And tada! We have sum a range with errors in Excel using the combination of the `IFERROR` function and the `SUM` function.

And that’s pretty much it! We have discussed three simple and easy methods to sum a range with errors in Excel. Now you can apply any of these methods whenever you encounter the same issue.

Are you interested in learning more about what Excel can do? You can now use the SUMIF function, AGGREGATE function, and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

## How to Use IF Statement Between Two Numbers or Dates

This guide will explain how to use the IF statement between two numbers or dates using the IF…

## How to Calculate Percentage as a Proportion in Excel

This guide will discuss how to calculate percentage as a proportion in Excel.  In Excel, calculating the percentage…

## How to Calculate Percent Change in Excel

In this guide, we will explain how you can use a simple formula to calculate percent change in…

## How to Use Wildcard in FILTER Function in Excel

This guide will explain how to use wildcards in the FILTER function in Excel. The rules for using…