excel - 如果满足多个条件，则将重复的单元格值计为一个

Question

我正在尝试获取总数 计数 条目数 每月 基于状态(“已解决”)和 不同的代码 .如果该特定代码中其他条目的其他状态尚未解决，则不应计入。请参阅下面的示例和预期的输出。

MONTH   |CODE  |CONTRIBUTION|STATUS
JANUARY |CS-111|500         |Settled
JANUARY |CS-111|500         |Settled
FEBRUARY|CS-131|200         |Settled    
FEBRUARY|CS-131|200         |Waiting
FEBRUARY|CS-141|300         |Settled
MARCH   |CS-151|400         |Waiting
MARCH   |CS-161|700         |Settled

样本输出:

January  | 1  | 1 because same code and status
February | 1  | 1 because only CS-141 is settled, the second entry of CS-131 is Waiting
March    | 1  | 1 because only CS-161 is settled

这是我基于 this 的公式问题:

=SUMPRODUCT(--($A$2:$A$8="January"),--(COUNTIFS($A$2:$A$8,$A$2:$A$8,$B$2:$B$8,$B$2:$B$8,$D$2:$D$8,"Settled")=0))

使用上面的公式，我得到 2 作为一月的输出，而不是 1. 1，因为它们具有相同的代码和状态。您能否帮我应用哪个概念来获得预期的输出？希望我的问题很清楚。非常感谢。

抱歉，如果以前有人问过这个问题。

Answer 1

想象一下这个设置:


G2 中的公式:

=SUM(--(FREQUENCY(IF(($A$2:$A$8=G2)*($D$2:$D$8="Settled"),IF(COUNTIFS($B$2:$B$8,$B$2:$B$8,$D$2:$D$8,"Waiting")=0,MATCH($B$2:$B$8,$B$2:$B$8,0))),ROW($B$2:$B$8)-ROW($B$2)+1)>0))

Note: It's an array formula and needs to be confirmed through Ctrl+Shift+Enter

拖累...