excel - 如果 2 或 3 列包含特定文本，则需要对列求和

Question

我有以下数据集，并且想要在任何单元格中添加反射(reflect)“ABC”的值。

      Column1      Column 2      Column 3     Column 4   Column 5   
      ABC is good    CNN           $150         ABC       NBA
      Better life    N-H           $40          LIT       MNM
      Nice Job       ABC is good   $35          MN        ABC
      Poor           H-I           $200         ITL       ABC
      Best           TI            $120         SQL       ABC  
      Poor life      N-T           $40          LT        NM
      Great          BE            $800         ABC       BEF

它应该返回的总和是 $150+$35+200+120+$400 = $905，因为单元格中的某处有文本“ABC”。我尝试使用 sumif(find) 公式，但给出了值错误。有什么想法吗？

Answer 1

简短回答

使用这个数组公式:

=SUMPRODUCT(IF(IF(LEN(SUBSTITUTE(A:A,"ABC",""))<LEN(A:A),1,0)+IF(LEN(SUBSTITUTE(B:B,"ABC",""))<LEN(B:B),1,0)+IF(LEN(SUBSTITUTE(D:D,"ABC",""))<LEN(D:D),1,0)+IF(LEN(SUBSTITUTE(E:E,"ABC",""))<LEN(E:E),1,0)>0,1,0),C:C)

注意:数组公式通过 ctrl + shift + enter 输入

说明

测试单元格是否包含 ABC我们可以使用SUBSTITUTE forumla 与 LEN 结合测试字符串长度之间的差异:

LEN(SUBSTITUTE(A:A,"ABC",""))<LEN(A:A)

然后我们可以将其包装在 IF 中语句来获取一个很好的 1 和 0 数组

IF(IF(LEN(SUBSTITUTE(A:A,"ABC",""))<LEN(A:A),1,0)

如果我们将其映射到您的数据，它将如下所示:

IF(IF(LEN(SUBSTITUTE(A:A,"ABC",""))<LEN(A:A),1,0) = {0, 1, 0, 0, 0, 0, 0, 0}
IF(IF(LEN(SUBSTITUTE(B:B,"ABC",""))<LEN(B:B),1,0) = {0, 0, 0, 1, 0, 0, 0, 0}
IF(IF(LEN(SUBSTITUTE(D:D,"ABC",""))<LEN(D:D),1,0) = {0, 1, 0, 0, 0, 0, 0, 1}
IF(IF(LEN(SUBSTITUTE(E:E,"ABC",""))<LEN(E:E),1,0) = {0, 0, 0, 1, 1, 1, 0, 0}
                                                 += {0, 2, 0, 2, 1, 1, 0, 1}

然后我们要做的就是检查数组中的数字是否为 >0并使用 SUMPRODUCT 将其乘以 C 列:

      {0, 2,   0,  2,  1,   1,   0,  1  }
>0    {0, 1,   0,  1,  1,   1,   0,  1  }
*C:C  {0, 150, 40, 35, 200, 120, 40, 800}
=     {0, 150, 0,  35, 200, 120, 0,  800}
-----------------------------------------
SUM = 1305