多条件筛选计数

风在脸上舞蹈

sumproduct函数始终没弄懂，想要求一下满足三个条件的筛选结果里不重复项个数，始终得不出正确的数，高手指点下

高个子

1. =SUMPRODUCT(IFERROR(1/COUNTIFS(A2:A74,A2:A74,B2:B74,"免费",C2:C74,"三日游",D2:D74,"自助"),))

复制代码

hugeinfo

=COUNTA(UNIQUE(FILTER(A2:A74,(B2:B74="免费")*(C2:C74="三日游")*(D2:D74="自助"))))

袁勇

=SUMPRODUCT((B2:B74="免费")*(C2:C74="三日游")*(D2:D74="自助")/COUNTIFS(A:A,A2:A74,B:B,B2:B74,C:C,C2:C74,D:D,D2:D74))

jivy1212

1. =COUNT(FREQUENCY(ROW(A:A),MATCH(A2:A666,A2:A666,)*(B2:B666&D2:D666&C2:C666="免费自助三日游"))^0)-1

复制代码

袁勇

数组公式：

=COUNT(0/(MATCH(A2:A74&"免费三日游自助",A1:A74&B1:B74&C1:C74&D1:D74,)=ROW(2:74)))

quqiyuan

1. =SUMPRODUCT(IFERROR(1/COUNTIFS(B:B,"免费",C:C,"三日游",D:D,"自助",A:A,A2:A74),0))

复制代码

Tony412

=COUNT(0/FREQUENCY(ROW(A:A),MATCH(A2:A74,A2:A74,)*(B2:B74=B2)*(C2:C74=C2)*(D2:D74=D2)))-1

静听溪流

=SUM(IFERROR(1/COUNTIFS(B$2:B$74,"免费",C$2:C$74,"三日游",D$2:D$74,"自助",A$2:A$74,A$2:A$74),))

风在脸上舞蹈

hugeinfo 发表于 2023-6-7 10:54
=COUNTA(UNIQUE(FILTER(A2:A74,(B2:B74="免费")*(C2:C74="三日游")*(D2:D74="自助"))))

uinique函数要2019才支持吧