|
楼主 |
发表于 2019-1-21 20:45
|
显示全部楼层
jyieo 发表于 2019-1-19 20:40
=SUMPRODUCT((CLEAN(TRIM($L$2:$L$11))*1=LOOKUP(9^9,$A$2:$A2))*(CLEAN(TRIM($M$2:$M$11))=$B2)*(CLEAN(TR ...
=SUMPRODUCT((CLEAN(TRIM($L$2:$L$11))*1=LOOKUP(9^9,$A$2:$A2))*(CLEAN(TRIM($M$2:$M$11))=$B2)*(CLEAN(TRIM($N$2:$N$11))=C$1)*$O$2:$O$11)
请问
1.为什么我将范围加大,就会报错呢?
=SUMPRODUCT((CLEAN(TRIM($L$2:$L$2000))*1=LOOKUP(9^9,$A$2:$A2))*(CLEAN(TRIM($M$2:$M$2000))=$B2)*(CLEAN(TRIM($N$2:$N$2000))=C$1)*$O$2:$O$2000)
2.而这样更改后,不会报错,但为什么得到的结果为 0?
=SUMPRODUCT((CLEAN(TRIM($L$2:$L$2000))=LOOKUP(9^9,$A$2:$A2))*(CLEAN(TRIM($M$2:$M$2000))=$B2)*(CLEAN(TRIM($N$2:$N$2000))=C$1)*$O$2:$O$2000) |
|