用COUNTIFS代替SUMPRODUCT

Question

我有一个使用SUMPRODUCT的公式，如果满足了一组条件，就给出行号。由于我从其中获取数据的文件有13000行，因此它变得很费时。

下面是SUMPRODUCT公式：

=SUMPRODUCT((N30=Hierarchy!$B$1:$B$15000)*(D30>=Hierarchy!$I$1:$I$15000)*(D30<=Hierarchy!$J$1:$J$15000)*ROW(Hierarchy!$U$1:$U$15000))

我试图用下面的公式(通过COUNTIFS)来替代以前的公式--为什么它不能工作呢？

=COUNTIFS(Hierarchy!$B$1:$B$15000, N30, Hierarchy!$I$1:$I$15000, "<="&D30, Hierarchy!$J$1:$J$15000,">="&D30)

Answer 1

COUNTIFS不会返回一行，它将返回找到的匹配数(只有一个匹配，对吗？)匹配公式将完成您正在寻找的任务：

=MATCH(1,(N30=$B$1:$B$15000)*(D30>=$I$1:$I$15000)*(D30<=$J$1:$J$15000),0)

这是一个数组公式，因此您需要使用CTRL+SHIFT+ENTER来使其工作。

我不知道这能节省多少时间。如果多行匹配标准，则匹配公式将显示第一行，而SUMPRODUCT公式将显示所有匹配行的和。

编辑:我从Contextures获得了匹配公式：http://blog.contextures.com/archives/2012/07/12/check-multiple-criteria-with-excel-index-and-match/