一题多解之两表条件汇总：MMULT与SUMIFS+COUNTIFS的对抗

CY7CY7CY7

=SUM(N(INDIRECT(TEXT((MATCH(E$5:E$23,I$5:I$14,)*(YEAR(C$5:C$23)=S4)+4)&10,"R00C00"),0)))
=SUM((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)

CY7CY7CY7

=SUM(LOOKUP(MATCH(E$5:E$23,I$5:I$14,),ROW($1:$10),J$5:J$14)*(YEAR(C$5:C$23)=S4))
=SUM((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)

dengjq2000

结合11楼与12楼，把等同的SUMPRODUCT函数跟帖出来。
单元格T2公式：

1. =SUMPRODUCT(N(INDIRECT(TEXT((MATCH(E$5:E$23,I$5:I$14,)*(YEAR(C$5:C$23)=S4)+4)&10,"R00C00"),0)))

复制代码

或：

1. =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数组公式：

1. =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)

复制代码

或公式：

1. =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)

复制代码

CY7CY7CY7

=SUM(LOOKUP(ROUNDUP(SEARCH(E$5:E$23,PHONETIC(I$5:I$14))/3,0),ROW($1:$10),J$5:J$14)*(YEAR(C$5:C$23)=S4))
=SUM((LEFT(TRIM(MID(SUBSTITUTE(PHONETIC($I$5:$K$14),E$5:E$23,REPT(" ",100)),100,100)))="是")*(YEAR(C$5:C$23)=S4)*G$5:G$23)

xutianlin

赞。。。楼主函数功力确实了得

dengjq2000

xutianlin 发表于 2014-12-16 13:27
赞。。。楼主函数功力确实了得

呵呵……是呀！这题通过函数来实现还真有很多种方法呀！
您也跟帖去解法哟！

cmxxccxx

就针对这个例子可以这样数组公式：

1. =SUM((YEAR(C$5:C$23)=S4)*VLOOKUP(T(IF({1},E$5:E$23)),I$5:J$14,2,))

复制代码

1. =SUM((YEAR(C$5:C$23)=S4)*G$5:G$23*(VLOOKUP(T(IF({1},E$5:E$23)),I$5:K$14,3,)="是"))

复制代码

念无

如果含有日期条件的话，sumif还是要短一些的

1. =SUM(SUMIF(I$5:I$14,E$5:E$23,J$5:J$14)*(YEAR(C$5:C$23)=$S4))

复制代码

1. =SUM(COUNTIFS(I$5:I$14,E$5:E$23,K$5:K$14,"是")*G$5:G$23*(YEAR(C$5:C$23)=$S4))

复制代码

CY7CY7CY7

1. =SUM((YEAR(C$5:C$23)=S4)*(E$5:E$23=TRANSPOSE(I$5:I$14))*TRANSPOSE(J$5:J$14))

复制代码

1. =SUM(G$5:G$23*(YEAR(C$5:C$23)=S4)*(E$5:E$23=TRANSPOSE(I$5:I$14))*TRANSPOSE(K$5:K$14="是"))

复制代码

突然想起上次因出差停止做这题了，继续