Practical Excel Tips Sharing: How to Ignore Error Values ​​for Sum

"How to sum data that contains error values? If we sum it directly, the result obtained is also an error value. What should we do?" Don't worry, today this article will share with you three methods Let’s take a look at the method of summing directly ignoring the error values ​​using formulas!

#Quote the existing receipt amount according to the corresponding order number. I believe many friends can handle this problem and can use the VLOOKUP function to solve it.

What we are going to discuss today is how to sum data that contains incorrect values.

If you sum directly, the result is also an error value, as shown below:

For this kind of data that needs to be summed with error values situation, today the veteran will share how to deal with three formulas. I hope that everyone can grasp the ideas for solving problems through the analysis of the formulas, draw inferences from one example, and gain a comprehensive understanding.

Formula routine 1: SUM IFERROR combination

The SUM function is indispensable for summation, and the IFERROR function is naturally indispensable for dealing with error values. Therefore, by combining these two functions, you can achieve the purpose of summing ignoring error values. The formula is: =SUM(IFERROR(C2:C18,0))

Formula analysis:

The IFERROR function has only two parameters, and the format is IFERROR (data to be processed, the result returned when the data is an error). In this example, the data to be processed is the area C2:C18 where the receipt amount is located. When the receipt amount is an error value, 0 is returned. Therefore, the result of IFERROR(C2:C18,0) is:

Because the first parameter of IFERROR refers to the cell range, this will get a set of numbers, so When using the SUM function to perform sums, you need to hold down the Ctrl Shift and Enter keys at the same time to generate an array formula. At this time, curly brackets will automatically appear on both sides of the formula, and the formula will obtain the correct result.

Formula routine 2: SUMIF function

The SUMIF function is also a very commonly used function. Its basic function is to perform summation according to specified conditions. The format is SUMIF (condition area, condition, summation area). When the condition area and the summation area are consistent, the summation area can be omitted.

What is actually done in this example is to sum the data greater than 0. The error value is not even a number, so of course it does not meet the condition of being greater than 0. Therefore, the formula is =SUMIF(C2:C18,">0")

If the data you need to process are not all numbers greater than 0, you need to use the following Formula: =SUMIF(C2:C18,"<9e307")

9e307 means 9*10^307, which is almost the maximum value that Excel can accept. So data smaller than the maximum number are summed, and error values ​​can be ignored.

Formula routine 3: AGGREGATE function

Let’s talk about the AGGREGATE function, which is a very powerful multi-functional statistical function. It is better than the one mentioned before. The SUBTOTAL function is even more powerful! In this example we are only using one of its many features and ignoring the summation of error values.

This function has four parameters. The basic structure is AGGREGATE (function code, which data to ignore, data area to be counted, k value). The fourth parameter is only needed for certain special functions. , ignored in this example, the formula is: =AGGREGATE(9,6,C2:C18).

There are 19 options for the first parameter, 9 means summation.

There are 8 options for the second parameter, 6 means ignoring the error value.

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel Tips Sharing: How to Ignore Error Values ​​for Sum. For more information, please follow other related articles on the PHP Chinese website!