|
楼主 |
发表于 2014-12-16 11:43
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
结合11楼与12楼,把等同的SUMPRODUCT函数跟帖出来。
单元格T2公式:- =SUMPRODUCT(N(INDIRECT(TEXT((MATCH(E$5:E$23,I$5:I$14,)*(YEAR(C$5:C$23)=S4)+4)&10,"R00C00"),0)))
复制代码 或:- =SUMPRODUCT(LOOKUP(MATCH(E$5:E$23,I$5:I$14,),ROW($1:$10),J$5:J$14)*(YEAR(C$5:C$23)=S4))
复制代码 单元格U2数组公式:- =SUMPRODUCT((IF({1},T(INDIRECT(T(TEXT((MATCH(E$5:E$23,I$5:I$14,)+4)&11,"R00C00")),0)))="是")*(YEAR(C$5:C$23)=S4)*G$5:G$23)
复制代码 或公式:- =SUMPRODUCT((LOOKUP(MATCH(E$5:E$23,I$5:I$14,),ROW($1:$10),K$5:K$14)="是")*(YEAR(C$5:C$23)=S4)*G$5:G$23)
复制代码 |
|