使用具有多个日期条件和多个OR的COUNTIF

Question

我正试图为一个宠物救援组织生成养狗/猫报告。他们收集每种寄养动物的date_pickedup和date_returned，并想要历史月报。

我需要介绍以下情况

• 动物在报告月份被拾起并返回
• 动物在前一个月被捕获，在报告月份返回。
• 前一个月捡到的动物还没有回来
• 在报告月收到，还没有回来

在尝试为每个月创建一个过滤视图之后，我现在的重点是使用google表中一个单独的选项卡上的COUNTIF计算每个月的总数

=countIFS ('Animal tracking'!G4:G499,">1/31/19",'Animal tracking'!H4:H499,"<=2/28/19") 

+countIFS ('Animal tracking'!G4:G499,"<=1/31/19",'Animal tracking'!H4:H499,"<=2/28/19")

+countIFS ('Animal tracking'!G4:G499,"<=1/31/19",'Animal tracking'!H4:H499,">=3/1/19")

+countIFS ('Animal tracking'!G4:G499,">1/31/19",'Animal tracking'!H4:H499,">=3/1/19")  

我对结果进行了过多的计算，因为我对第四个场景的逻辑是不正确的，因为它返回了本月培育的动物。

Answer 1

您的计划是在帮助表上使用一系列COUNTIFS公式来计算历史月报。在我看来，这种特定的方法无法工作，主要是因为提议的公式太复杂，而且COUNTIFS没有提供所需的数据。

四层两级标准需要更详细的分析。还必须从公式中删除硬编码日期(同样适用于countifs)，并定义用户定义的报告周期日期，以便将公式链接到其中。

建议布局

​

​

• 报告所述期间已确定。
  • 只有一个单元格需要手动数据输入--报告期间的第一天。
  • 报告期间的最后一天使用eomonth()公式计算。

​

• 四项标准中的每一项分别进行评估，并在每项标准范围内分别审议这两个阶段(领取日期、返回日期)。
• 阶段单元格中的公式被设计成一个真实/错误的答案。
• 为每个标准添加第三列( "Match“)以评估阶段结果；如果两个阶段的结果都为TRUE，则”Match“将产生TRUE。
• 在每个“匹配”列的顶部，countif计数等于"TRUE“的单元格。结果是在本报告所述期间，每项标准适用的动物数量。
• “匹配”计数的总和是报告所述期间的动物总数。

数据公式(row#6及以下)可以根据需要复制。

假设

1. 单元格M1和N1必须格式化为日期
2. 数据启动row#6，并在row#300之前结束(尽管这显然可以更改)。
3. 收件日期=G栏
4. 返回日期=H栏
5. "Match“列中"TRUE”单元格的高亮显示仅供参考；使用条件格式完成。

单元内容及公式

M1:报告月份(开始) Manual data entry

N1:报告月份(结束) =eomonth(M1,0)

M3:动物计数-报告月=sum(O3:Z3)

Q3:总Criteria#1 =countif(Q6:Q300,TRUE)

T3:总Criteria#2 =countif(T6:T300,TRUE)

W3:总Criteria#3 =countif(W6:W300,TRUE)

Z3:总Criteria#4 =countif(Z6:Z300,TRUE)

Q4: Proof#1 =COUNTA(Q6:Q300)-countif(Q6:Q300,FALSE)-Q3

T4: Proof#2 =COUNTA(T6:T300)-countif(T6:T300,FALSE)-T4

W4: Proof#3 =COUNTA(W6:W300)-countif(W6:W300,FALSE)-W5

Z4: Proof#4 =COUNTA(Z6:Z300)-countif(Z6:Z300,FALSE)-Z6

O6: P/Up#1 =and(G6>=$M$1,G6<=$N$1)

P6: Rtn#1 =and(G6>=$M$1,G6<=$N$1)

Q6: Match#1 =And(O6=TRUE,P6=TRUE)

R6: P/Up#2 =G6<$M$1

S6: Rtn#2 =and(H6>=$M$1,H6<=$N$1)

T6: Match#2 =And(R6=TRUE,S6=TRUE)

U6: P/Up#3 =G6<$M$1

V6: Rtn#3 =or(H6>$N$1,isblank(H6))

W6: Match#3 =And(U6=TRUE,V6=TRUE)

X6: P/Up#4 =and(G6>=$M$1,G6<=$N$1)

Y6: Rtn#4 =or(H6>$N$1,isblank(H6))

Z6: Match#4 =And(X6=TRUE,Y6=TRUE)