excel - SUMPRODUCT 计算唯一文本值

Question

我有一个数据集，大致如下所示:

OpenDate      Name      FillDate      DaysToFill
12/05/13      Samuel        -           Open
01/01/14      Anne      01/16/14        16
01/12/14      Mike      01/25/14        13
01/28/14      Anne      01/31/14         3

我正在为指标创建一个仪表板。有一个数据验证列表，用户可以在其中选择他们想要的信息的月份。这一选择会触发两个单元格的变化——R2 和 R3。 R2 对应月份并生成该月的数值(Jan =1、Feb = 2 等)，R3 生成当年该月的最后一天(Jan = 01/31/14、Feb = 02/28/14)。

我有一个公式可以计算满足以下任一条件的条目数:

(i) DaysToFill > 0  AND FillDate is in the month chosen (in R2)

或者

(ii) DaysToFill = "Open" AND OpenDate < last day of month chosen (in R3)

我目前有以下 sumproduct 公式来计算:

=SUMPRODUCT(--(ISNUMBER(DATA!I2:I30)),--(MONTH(DATA!I2:I30)=Sheet1!R2),

--(DATA!J2:J30>0))+SUMPRODUCT(--(ISNUMBER(DATA!C2:C30)),

--((DATA!C2:C30)<=Sheet1!R3),--(DATA!J2:J30="Open"))

哪里

C 列 = OpenDate；第一列 = 填充日期； J 列 = DaysToFill，H 列(此处未使用)是名称

这一切都工作正常，但是我需要一种方法来在上述公式上添加条件 - 我需要一种方法来计算满足这些条件的唯一名称。因此对于上述数据，如果用户选择一月，我的公式的输出是 4 [3 满足 (i) 中的条件和 1 满足 (ii) 中的条件]。但是，如果我们增加了一层独特的招聘人员，我应该得到 3 个(因为安妮被计算了两次)。我很难知道如何解决这个问题。如有任何帮助，我们将不胜感激。

Answer 1

您可以使用这个“数组公式”

=SUM(IF(FREQUENCY(IF(ISNUMBER(DATA!I2:I30)*(MONTH(DATA!I2:I30)=Sheet1!R2)*(DATA!J2:J30>0)+ISNUMBER(DATA!C2:C30)*(DATA!C2:C30<=Sheet1!R3)*(DATA!J2:J30="Open"),IF(H2:H30<>"",MATCH(H2:H30,H2:H30,0))),ROW(H2:H30)-ROW(H2)+1),1))

使用CTRL+SHIFT+ENTER确认

请注意，我不确定您的原始公式是否有缺陷，因为 DATA!J2:J30>0将返回TRUE对于任何包含“Open”的单元格。如果J2:J30，这个额外的条件会实现什么吗？始终包含日期或“打开”，该部分将为所有内容返回 TRUE......