Sumproduct检查文本是否为命名范围的名称，并返回这些数字 [英] Sumproduct check if text is name of a named range and return those numbers

问题描述

用户可以在单元格中输入数字或名称.当我输入一个数字时，sumproduct工作正常，但是当我要检查一个命名范围的名称是否在单元格内部时，它不会返回任何内容.

=SUMPRODUCT((Data!A:A=A7)*((Data!C:C=V7)+(Data!C:C=W7)),Data!H:H)

Data!A:A是帐号，Data!C:C是国家ID. Data!H:H是需要累加的销售额.

我有一些命名范围，并在其上附加了数字

• 第一范围:4、12、15
• 第二范围:7、38、65

如何检查单元格中是否有命名范围的sumproduct，然后应用该命名范围数组进行求和?

解决方案

根据我的理解，您目前有两个命名范围:

根据您的屏幕截图，看起来您正在寻找此Array公式(使用Ctrl+Shift+Enter输入):

=SUMPRODUCT(--($M$2:$M$13=A2),--IF(ISNUMBER(B2),($N$2:$N$13=B2),ISNUMBER(MATCH($N$2:$N$13,INDIRECT(B2),0))),$O$2:$O$13)

查看以下结果-我在A列和P-S列中添加了颜色编码，以显示该公式的工作原理(即，SUMPRODUCT计算中包括/排除了哪些行):

the user is able to enter either a number or a name into a cell. When i put a number, sumproduct is working fine, but when i want it to check if the name of a named range is inside the cell, it doesn't return anything.

=SUMPRODUCT((Data!A:A=A7)*((Data!C:C=V7)+(Data!C:C=W7)),Data!H:H)

Data!A:A is an accountnumber and Data!C:C is the countryID. Data!H:H is the sales that need to be added up.

I have a few named ranges with number attached to them

• FirstRange: 4, 12, 15
• SecondRange: 7, 38, 65

How can I check inside my sumproduct if the cell has a named range and then apply that named range array to be summed up?

解决方案

As per my understanding, you currently have two named ranges:

As per your screenshot, it looks like you are looking for this Array formula (enter it with Ctrl+Shift+Enter):

=SUMPRODUCT(--($M$2:$M$13=A2),--IF(ISNUMBER(B2),($N$2:$N$13=B2),ISNUMBER(MATCH($N$2:$N$13,INDIRECT(B2),0))),$O$2:$O$13)

See the results below - I have added a color-coding in column A and columns P-S to show how the formula works (i.e. which rows are being included / excluded into the SUMPRODUCT calculation):

这篇关于Sumproduct检查文本是否为命名范围的名称，并返回这些数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！