如何对一列中的值进行求和,并排除不同列中具有重复文本值的单元格?

如何对一列中的值进行求和,并排除不同列中具有重复文本值的单元格?

我手头有一个基本问题。在下面的示例表中,有一个文本值列和一个数字值列:

A
果汁 5
花生 2
核桃 3
果汁 5
菠萝 8

我想对数字-值列中的数字求和,排除同一行中名称-值列中具有相同值的数字(即 juice),总和为 18。显然,每次给出名称时,数字都与名称相对应,这是理所当然的。

如果数字也不同,公式中需要做出哪些改变呢?如下所示:

A
果汁 5
花生 2
核桃 3
果汁 6
菠萝 8
果汁 5

在这种情况下,我想排除一个完全重复:果汁-5,并在计算中将其算作一个实例。总和中的答案现在是 24。

编辑:我有一张类似的表格:

1

下面我根据 A 列中的每个首字母计算数字的总和。例如:
C10 =SUMPRODUCT((A1:A8="B") *(C1:C8))

我想排除所有三个变量的所有重复项(即黄色),将它们算作一个。因此,C10 将是 5,而不是 7。我应该在公式中添加什么?

答案1

您可以使用 SUMPRODUCT 和 COUNTIFS 作为除数。这样,​​只需将每个数的 1/2juice 5相加,结果就只有5

=SUMPRODUCT(B1:B6/COUNTIFS(A1:A6,A1:A6,B1:B6,B1:B6))

在此处输入图片描述

假设有一个动态数组公式UNIQUE:

=SUM(INDEX(UNIQUE(A1:B6),0,2))

这将返回唯一值并对第二列求和。

在此处输入图片描述


根据您的编辑:

=SUMPRODUCT(((A1:A6="B") *(C1:C6))/(COUNTIFS(B1:B6,B1:B6,C1:C6,C1:C6,A1:A6,"=B")+(A1:A6<>"B")))

动态数组版本:

=SUM(INDEX(UNIQUE(FILTER(A1:C6,A1:A6="B")),0,3))

答案2

如何使用一个或两个辅助列来检测重复项?

假设您有 A 到 C 中的数据,并且如果所有三个 A 到 C 的值之前都一起出现过,那么辅助列应该为零,如果不是,则为 C 中的值。现在,如果您可以对数据进行排序(自定义排序,首先按 A 进行多级排序,然后按 B 然后按 C 进行多级排序),那么所有重复项将始终位于彼此的正下方。

因此在 D1 中输入(并复制下来)

=IF(AND(A1=A2,B1=B2,C1=C2),0,C2)

现在按照您的示例对 D 列进行求和。

如果无法排序,则必须添加另一个帮助列(例如 E),内容如下:

=A1 & B1 & C1

有了 D1 =C1, D2 就变成(向下复制)

=IF(ISNUMBER(MATCH(E2,$E$1:E1,0)),0,C2)

答案3

对于第二个问题,可以参考这个公式:

=SUMPRODUCT(IFERROR((MATCH(IF($A$1:$A$8=E1,$B$1:$B$8&$C$1:$C$8,NA()),IF($A$1:$A$8=E1,$B$1:$B$8&$C$1:$C$8,NA()),)=ROW($1:$8))*$C$1:$C$8,0))

在此处输入图片描述

相关内容