如果满足多个条件，则将重复的单元格值计数为一个 [英] Count duplicate cell values as one if multiple criteria are met

问题描述

我正在尝试根据状态（已结算）和不同的代码，每月 获取条目的总数数量。如果该代码中其他条目的其他状态尚未确定，则不应计入。

  MONTH | CODE | CONTRIBUTION | STATUS 
 1月| CS-111 | 500 |已结算
一月| CS-111 | 500 |已结算
 2月| CS-131 | 200 |已结算
 2月| CS-131 | 200 |正在等待
 2月| CS- 141 | 300 |已结算
 3月| CS-151 | 400 |正在等待
 3月| CS-161 | 700 |已结算
  

样本输出：

  1 | 1，因为相同的代码和状态
 1 | 1因为仅结算了CS-141，所以CS-131的第二个条目是Waiting 
 1 | 1，因为仅CS-161已结算
  

这是我基于

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，等待）= 0，MATCH（$ B $ 2：$ B $ 8，$ B $ 2：$ B $ 8,0））），ROW（$ B $ 2：$ B $ 8）-ROW（$ B $ 2）+1）> 0））
  

注意：这是一个数组公式，需要通过 Ctrl + Shift + Enter 进行确认p>

向下拖动...

I am trying to get the total count of entries per month based on the status ("Settled") and the distinct code. If the other status of the other entries in that certain code is not yet settled, it shouldn't count. Please see example below and the expected output.

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

Sample output:

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 is my formula based on this question:

=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))

With the formula above, I get 2 as the output for January and not 1. 1 because they have the same code and status. Could you kindly help me which concept I should apply to get the expected output? Hope my question is clear. Thank you very much.

Apologies if this question has been asked before.

解决方案

Imagine this setup:

Formula in 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

Drag down...

这篇关于如果满足多个条件，则将重复的单元格值计数为一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！