求助！公式实现多列多条件去重统计

蔡高八斗

需要在限定D列和E列条件下，统计ABC三列去重后发明人数量

十指

上传压缩后的Excel文件，说明需求，模拟结果

愛如指尖沙

附件如上，请查收！
工作簿2.rar (31.55 KB, 下载次数: 98)

2021-11-17 12:13 上传

点击文件名下载附件
工作簿2.rar

阮寳

I3=SUM(IFERROR(MMULT(($D$2:$D$522<=2019)*($E$2:$E$522=$H3),{1,1,1})/CHOOSE({1,2,3},COUNTIFS($A$2:$A$522,$A$2:$A$522,$D$2:$D$522,"<=2019",$E$2:$E$522,$H3),COUNTIFS($B$2:$B$522,$B$2:$B$522,$D$2:$D$522,"<=2019",$E$2:$E$522,$H3),COUNTIFS($C$2:$C$522,$C$2:$C$522,$D$2:$D$522,"<=2019",$E$2:$E$522,$H3)),0))

J3=SUM(IFERROR(MMULT(($D$2:$D$522=2020)*($E$2:$E$522=$H3),{1,1,1})/CHOOSE({1,2,3},COUNTIFS($A$2:$A$522,$A$2:$A$522,$D$2:$D$522,2020,$E$2:$E$522,$H3),COUNTIFS($B$2:$B$522,$B$2:$B$522,$D$2:$D$522,2020,$E$2:$E$522,$H3),COUNTIFS($C$2:$C$522,$C$2:$C$522,$D$2:$D$522,2020,$E$2:$E$522,$H3)),0))

K3=SUM(IFERROR(MMULT(($D$2:$D$522=2021)*($E$2:$E$522=$H3),{1,1,1})/CHOOSE({1,2,3},COUNTIFS($A$2:$A$522,$A$2:$A$522,$D$2:$D$522,2021,$E$2:$E$522,$H3),COUNTIFS($B$2:$B$522,$B$2:$B$522,$D$2:$D$522,2021,$E$2:$E$522,$H3),COUNTIFS($C$2:$C$522,$C$2:$C$522,$D$2:$D$522,2021,$E$2:$E$522,$H3)),0))

都是数组公式下拉，技穷了，期待高手更好的思路

用户李明

我就不会MM函数 我的天 就跟看天书一样空着的城市怎么弄

丁叮町

I3 数组公式
=COUNT(0/(N(MATCH($A$2:$A$600&$B$2:$B$600&$C$2:$C$600,$A$2:$A$600&$B$2:$B$600&$C$2:$C$600,)=ROW($1:$599))*($D$2:$D$600<2019)*($E$2:$E$600=$H3)))

J3 数组公式
=COUNT(0/(N(MATCH($A$2:$A$600&$B$2:$B$600&$C$2:$C$600,$A$2:$A$600&$B$2:$B$600&$C$2:$C$600,)=ROW($1:$599))*($D$2:$D$600>=2019)*($D$2:$D$600<2020)*($E$2:$E$600=$H3)))

K3 数组公式
=COUNT(0/(N(MATCH($A$2:$A$600&$B$2:$B$600&$C$2:$C$600,$A$2:$A$600&$B$2:$B$600&$C$2:$C$600,)=ROW($1:$599))*($D$2:$D$600>=2020)*($D$2:$D$600<2021)*($E$2:$E$600=$H3)))