[已解决]请帮忙看看SHEET2的公式错在哪了

demon558

要计算的是sheet2中的数字在sheet1出现的次数，计算结果老是不对
Book.rar (21.79 KB, 下载次数: 10)

2013-5-17 20:58 上传

点击文件名下载附件

最佳答案

月排行榜 / 总排行榜

as0810114

2013-5-17 21:10

sheet2   H2  输入公式

1. =SUMPRODUCT((Sheet1!$A$2:$A$201=Sheet2!A2)*(Sheet1!$B$2:$B$201=Sheet2!B2)*(Sheet1!$C$2:$C$201=Sheet2!C2))

复制代码

跳转到最佳答案楼层

as0810114

sheet2   H2  输入公式

1. =SUMPRODUCT((Sheet1!$A$2:$A$201=Sheet2!A2)*(Sheet1!$B$2:$B$201=Sheet2!B2)*(Sheet1!$C$2:$C$201=Sheet2!C2))

复制代码

xuyouxm

1. =SUM(IF((Sheet1!$A$2:$A$500=Sheet2!A6)*(Sheet1!$B$2:$B$500=Sheet2!B6)*(Sheet1!$C$2:$C$500=C6),1))

复制代码

=SUMPRODUCT((Sheet1!$A$2:$A$500=Sheet2!A4)*(Sheet1!$B$2:$B$500=Sheet2!B4)*(Sheet1!$C$2:$C$500=C4))

云影

1. =SUMPRODUCT(N(Sheet1!A$2:A$201&Sheet1!B$2:B$201&Sheet1!C$2:C$201=A2&B2&C2))

复制代码

不知道你要什么样的结果，猜一下

demon558

as0810114 发表于 2013-5-17 21:10
sheet2   H2  输入公式

=SUM(N(MMULT((data!$B$2:$F$2000=B2)+(data!$B$2:$F$2000=C2)+(data!$B$2:$F$2000=D2)+(data!$B$2:$F$2000=E2)+(data!$B$2:$F$2000=F2),ROW(1:5)^0)=5))

这段公式在别的地方就可以用，我拉过来改了点位置就不对了，为什么啊，求解