符合多条件（条件有横向和纵向）查找数据

solextrade

=SUMIFS(INDEX(A:G,,MATCH(K4,$3:$3,)),A:A,I4,B:B,J4)

一烟难禁

嘿嘿，我也来凑凑热闹=SUM(FILTER($C$4:$G$10,$C$3:$G$3=K4)*($A$4:$A$10=I4)*($B$4:$B$10=J4))

一烟难禁

SUMIFS也是可以的

1. =SUMIFS(OFFSET($B$2:$B$8,,MATCH(K2,$C$1:$G$1,)),$A$2:$A$8,I2,$B$2:$B$8,J2)

复制代码

hidyzho

w4275 发表于 2022-5-25 16:47
=XLOOKUP(I4&J4,$A$4:$A$10&$B$4:$B$10,XLOOKUP(K4,$C$3:$F$3,$C$4:$F$10))

XLOOKUP原里应该这样写。。。学到了！感谢~~

hidyzho

solextrade 发表于 2022-5-25 17:01
=SUMIFS(INDEX(A:G,,MATCH(K4,$3:$3,)),A:A,I4,B:B,J4)

SUMIFS这个思路清奇啊...打死我也想不到，谢谢指点~~~

hidyzho

一烟难禁 发表于 2022-5-25 17:48
嘿嘿，我也来凑凑热闹=SUM(FILTER($C$4:$G$10,$C$3:$G$3=K4)*($A$4:$A$10=I4)*($B$4:$B$10=J4))

先提取再加总，啊啊啊，我的猪脑子，谢谢亲亲

tiger1976

=INDEX($C$4:$G$10,MATCH(1,($A$4:$A$10=I4)*($B$4:$B$10=J4),0),MATCH(K4,$C$3:$G$3,0))三建下拉