SUM 函数示例:按条件统计唯一值

概括

要统计符合一个或多个条件的唯一值，可以使用基于 UNIQUE、LEN和FILTER 的公式。在所示示例中，H7 中的公式为：

=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))

返回 3，因为 B6:B15 中有三个与 Omega 相关的唯一名称。

注意：此公式需要动态数组公式，该公式仅在Excel 365中可用。在旧版本的 Excel 中，您可以使用更复杂的替代公式。

=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))

在本例中，目标是统计满足一个或多个特定条件的唯一值。示例中，单元格 H7 中使用的公式为：

=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))

该公式的核心是使用 FILTER 函数应用筛选条件，并使用 UNIQUE 函数提取剩余的唯一值。从内到外，FILTER 函数用于应用筛选条件，并仅提取与“Omega”项目相关的名称：

FILTER(B6:B15,C6:C15=H6,"") // Omega names only

请注意， FILTER 函数中的if_empty参数被设置为空字符串("")，这对于我们统计最终结果的方式至关重要。FILTER 函数的结果是一个类似这样的数组：

{"Jim";"Jim";"Carl";"Sue";"Carl"}

接下来，使用 UNIQUE 函数删除重复项：

UNIQUE({"Jim";"Jim";"Carl";"Sue";"Carl"})

最终得到一个如下所示的新数组：

{"Jim";"Carl";"Sue"} // after UNIQUE

至此，我们已经得到了与 Omega 相关的唯一名称列表，现在只需要统计列表中的名称数量。原因将在下文解释，我们将使用 LEN 函数和 SUM 函数来实现这一点。为了便于理解，我们首先重写公式，使其包含该唯一名称列表：

=SUM(--(LEN({"Jim";"Carl";"Sue"})>0))

LEN 函数获取列表中每个元素的长度，并返回一个长度数组：

LEN({"Jim";"Carl";"Sue"}) // returns {3;4;3}

接下来，我们检查长度是否大于零：

LEN({3;4;3)>0 // returns {TRUE;TRUE;TRUE}

使用双重否定将 TRUE 和 FALSE 值强制转换为 1 和 0：

--({TRUE;TRUE;TRUE}) // returns {1;1;1}

最后，我们使用SUM 函数将结果相加：

=SUM({1;1;1}) // returns 3

请注意，由于我们会检查 UNIQUE 函数返回的每个项目的长度，因此符合条件的空单元格将被忽略。同样，如果 FILTER 函数返回空字符串（"")，长度为零），则该空字符串也不会被计入计数。

该公式是动态的，如果源数据发生变化，它将立即重新计算。

要根据多个条件统计唯一值，可以扩展 FILTER 函数内部的“include”逻辑。例如，要仅统计 6 月份 Omega 项目的唯一名称，请使用：

=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"),"")))>0))

这是一个使用布尔逻辑应用多个条件的示例。

可以使用COUNTA 函数编写一个更简单的公式。但是，需要注意的是，当没有匹配值时，COUNTA 函数会返回 1。这是因为当没有数据符合条件时，FILTER 函数会返回错误，而这个错误最终会被 COUNTA 函数计算在内。基本的 COUNTA 公式如下所示：

=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))

同样，当没有匹配的数据时，此公式将返回 1。它还会包含符合条件的空单元格。基于 LEN 和 SUM 的公式是更好的选择。

如果您使用的是不支持动态数组的旧版 Excel，则可以使用更复杂的公式。