如何对包含错误的值的命名范围求和? [英] How do I sum a named range of values that contains errors?

问题描述

我有一个Excel电子表格，可以从需要进行计算的其他来源导入数据.我需要使用的数据在一个命名范围内-恰好在C12:C36范围内-但它被称为"SumData".

I have an Excel spreadsheet which imports data from another source that I need to run a calculation on. The data I need to work with is in a named range - it happens to be in the range C12:C36 - but it's called "SumData".

此范围内的值包含许多错误，当前包括#NUM !！和#N/A，但可能包含其他错误.我需要对这些值求和.

The values in this range contain a number of errors currently including #NUM! and #N/A, but could potentially contain other errors. I need to sum these values.

我了解如何计算总和公式:

I understand how to do the sum formula:

=SUM(SumData)

或

=SUM(C12:C36)

我还可以使用IFERROR来检查错误:

I can also use IFERROR to check for errors:

=IFERROR(C12:C36, 0)

但是IFERROR只出现在检查我输入的公式的当前对应行.也就是说，如果在D12中输入了我的公式，则仅检查C12行中是否有错误，并返回值(如果C12包含错误，则返回0).

But IFERROR only appears to check the current corresponding row to the formula I'm entering. i.e. if my formula is being entered in D12, it only checks for error on row C12, and returns the value (or 0 if C12 contains an error).

如果我将两者结合起来做:

If I combine the two to do:

=SUM(IFERROR(SumData,0))

我没有获得有效的返回值.它基本上给了我最近的对应行-例如:

I don't get a valid return value. It basically gives me the nearest corresponding row - for instance:

    C      D
    -----------
12  #NUM!  
13  2      =SUM(IFERROR(SumData,0)) = 2 (I would expect this to produce 48)
14  5
15  7
16  #N/A
17  23
18  6
19  5

我在做什么错了?

推荐答案

=SUM(IF(ISNUMBER(SumData),SumData))

输入为数组公式...即使用CTRL + Shift + Enter而不是Enter.如果操作正确，Excel会在公式{}前后加上大括号.

entered as an array formula... ie using CTRL+Shift+Enter and NOT just Enter. If you do it correctly then Excel will put curly brackets around the formula{}.

这篇关于如何对包含错误的值的命名范围求和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！