Excel - 多列中的值的总和

apple   banana  apple
 52       12     35
 25       21     53
 44       65     41

How do I get the sum of all values in multiple columns that satisfy a condition (say looking for columns having apple as the header) and I can get the sum of all the values appearing under apple column. I need 250 as the answer. Is it possible to generate it using formulas? In other words, I look for headers containing apple and provide total of all the numbers that fall within the header.

如何获得满足条件的多列中的所有值的总和(比如查找具有apple作为标题的列),并且我可以获得出现在apple列下的所有值的总和。我需要250作为答案。是否可以使用公式生成它?换句话说,我查找包含apple的标题,并提供标题内所有数字的总和。

Thanks in advance.

提前致谢。

2 个解决方案

#1

Use this formula:

使用此公式:

=SUM(INDEX((A1:C1=E2)*A2:C4,))

As per your comments to find if the Word is in a longer string use:

根据您的意见,查找Word是否使用更长的字符串:

=SUM(INDEX((ISNUMBER(SEARCH( E2,A1:C1)))*A2:C4,))

#2

Sumproduct will do:

Sumproduct将做:

=SUMPRODUCT(A2:C4*(A1:C1="apple"))

Going with Scotts layout for fruits including the keyword:

使用包含关键字的水果的Scotts布局:

=SUMPRODUCT(A2:C4*ISNUMBER(SEARCH(E2,A1:C1)))
=SUMPRODUCT(A2:C4*ISNUMBER(FIND(E2,A1:C1)))

FIND if case matters and SEARCH if not.
For excluding the keyword (for being somewhere in the names) simply replace ISNUMBER with ISERROR

如果案件有问题,请查找,如果没有则进行搜索。要排除关键字(在名称中的某个位置),只需将ISNUMBER替换为ISERROR即可

EDIT:

When dealing with non-numeric values (such as text or errors) you can use this array formula:

处理非数字值(如文本或错误)时,可以使用此数组公式:

=SUM(IF(ISNUMBER(A2:C4),A2:C4)*ISNUMBER(SEARCH(E2,A1:C1)))

This is an array-formula and must be confirmed with ctrl+shift+enter!

这是一个数组公式,必须用ctrl + shift + enter确认!

#1