统计供应商每个月份不重复个数

star□囧□

各位新年好，请教下大神office 2019 的EXCEL公式，统计每个供应商=》每月（如1、2、3、4...）=》批号不重复个数，多谢了.

满坛皆为吾师

C4,三键右拉

1. =IF($A4="","",COUNT(0/FREQUENCY(IF((C$3=Sheet1!$C$2:$C$999)*($A4=TEXT(Sheet1!$L$2:$L$999,"m月")),--REPLACE(RIGHT(Sheet1!$N$2:$N$999,6),3,1,),""),ROW($999:$32100))))

复制代码

624134745

试试数据透视表的非重复计数？

limonet

=IF($A2>"",SUM(N(MATCH(IF((--LEFTB($A2,2)=MONTH(Sheet1!$C$2:$C$16))*(Sheet1!$A$2:$A$16=B$1),Sheet1!$D$2:$D$16),IF((--LEFTB($A2,2)=MONTH(Sheet1!$C$2:$C$16))*(Sheet1!$A$2:$A$16=B$1),Sheet1!$D$2:$D$16),)=ROW($1:$15)))-1,"")
右拉，下拉。

两蛋一瓜

使用透视表解决（加载到数据模型）

star□囧□

满坛皆为吾师 发表于 2021-2-22 12:48
批次每月都是001起头，B2数组三键，右拉下拉
=IF(A2="","",COUNT(0/FREQUENCY(IF((B$1=Sheet1!$A$2:$A$99) ...

将数据代入后有两个供应商与答案不正确，能否帮忙修正下那里出错了，谢谢。

小快刀

B2=SUM(IFERROR(1/COUNTIFS(Sheet1!$A$2:$A$15,Sheet2!B$1,Sheet1!$C$2:$C$15,">="&DATE(2021,SUBSTITUTE(Sheet2!$A2,"月",""),1),Sheet1!$C$2:$C$15,"<"&(DATE(2021,SUBSTITUTE(Sheet2!$A2,"月","")+1,1)),Sheet1!$D$2:$D$15,Sheet1!$D$2:$D$15),0))
数组方式确认，然后右拉、下拉