时间戳

首页 > 解决方案 > Excel Counifs - 添加简单的标准但不起作用

excel - Excel Counifs - 添加简单的标准但不起作用

问题描述

我有简单的计数来计算两个日期之间的未清项目，现在我想添加另一个条件但不起作用。两个代码都作为独立代码工作，但组合时会出错。

State   IssueDate   Due/Closed Date De      Date    Number  Number (A B C) only
Closed  12/12/2014  28/04/2015  DD      28/04/2015  1.00    
Closed  11/01/2019  04/03/2019  DD      04/03/2019  1.00    
Closed  05/01/2019  15/01/2019  DD      15/01/2019  2.00    
Closed  06/07/2016  28/10/2016  DD      28/10/2016  1.00    
Closed  20/10/2018  24/10/2018  DD      24/10/2018  1.00    
Closed  25/09/2016  27/09/2016  DD      27/09/2016  2.00    
Closed  24/07/2016  25/07/2016  DD      25/07/2016  3.00    
Closed  13/07/2018  22/07/2018  DD      22/07/2018  1.00    
Closed  05/07/2016  22/08/2016  A       22/08/2016  2.00    
Closed  02/04/2016  12/05/2016  B       12/05/2016  2.00    
Closed  14/07/2017  31/07/2017  C       31/07/2017  1.00    
Closed  03/05/2016  20/05/2016  DD      20/05/2016  1.00    
Closed  11/05/2017  18/05/2017  D       18/05/2017  1.00    
Open    09/06/2019  07/10/2019  XXX     07/10/2019  2.00

我的公式是计算打开数'=COUNTIFS($B$2:$B$50000,"<="&F2,$C$2:$C$50000,">="&F2) + COUNTIFS(A:A, "Open",C:C, "<="&F2)'，我试图添加COUNTIFS (F:F, ="A"). 我就是不能结合它。如果我知道如何添加 AI，我也会添加 B 和 C。

任何想法我做错了什么。

标签： excel

解决方案

将“A”放在H1中，然后将条件添加到原始公式中：

=COUNTIFS($B$2:$B$50000,"<="&F2,$C$2:$C$50000,">="&F2,F:F, "="&$H$1) + COUNTIFS(A:A, "Open",C:C, "<="&F2,F:F, "="&$H$1)

推荐阅读