WPS怎么统计满足几个条件并且是本月的完成数，具体请看附件

hypo236709

附件表格I列和J列已列好公式 ，还差一个统计本月的条件，要怎么加这个条件呢？

xtmyd5156

无4月数据，调整到了上月月初，月末来做为条件。

mjm04

按月份统计，一步到位的


=IFERROR(PIVOTBY(HSTACK(YEAR(C1:C89)&"年"&MONTH(C1:C89)&"月",B1:B89),A1:A89,D1:D89,COUNTA,3,1,,0,,D2:D89<>"未整站"),"")

mykitami

目前已经是4月了，数据为空
=BYCOL(COUNTIFS(B:B,H2,A:A,{"新","旧"},D:D,"*交*",C:C,"<="&EOMONTH(TODAY(),{-1;0}))*{-1;1},SUM)

袁勇

=SUM(COUNTIFS(B:B,H2,A:A,"新",D:D,{"整站交维","未单优均移交"},C:C,"<="&EOMONTH(TODAY(),{-1;0}))*{-1;1})

=SUM(COUNTIFS(B:B,H2,A:A,"旧",D:D,{"整站交维","未单优均移交"},C:C,"<="&EOMONTH(TODAY(),{-1;0}))*{-1;1})

袁勇

或：

1. =SUM(COUNTIFS($B:$B,$H2,$A:$A,LEFT(I$1),$D:$D,{"整站交维","未单优均移交"},$C:$C,"<="&EOMONTH(TODAY(),{-1;0}))*{-1;1})

复制代码

右拉下拉。

mykitami

将各月都一次性汇总出来
=PIVOTBY(B2:B89,HSTACK(TEXT(MONTH(C2:C89),"00月"),A2:A89),D2:D89,COUNTA,,1,,1,-2,ISNUMBER(MATCH(D2:D89,{"整站交维","未单优均移交"},)))

wyslw2004

=SUM(COUNTIFS(C:C,">"&EOMONTH(TODAY(),-1),C:C,"<="&EOMONTH(TODAY(),0),B:B,H2,A:A,"新",D:D,{"整站交维","未单优均移交"}))

秃头发师

=SUM(COUNTIFS(B:B,H2,A:A,"新",D:D,{"整站交维","未单优均移交"},C:C,">="&EOMONTH(TODAY(),-1)+1,C:C,"<="&EOMONTH(TODAY(),0)))

wyslw2004

袁勇 发表于 2025-4-1 14:35
=SUM(COUNTIFS(B:B,H2,A:A,"新",D:D,{"整站交维","未单优均移交"},C:C,"

学习了，把公式仔细折开，才明白{-1;0}和*{-1;1}一起用的妙处。赞赞赞！！！！！！