多条件数据求和公式怎么写

wshj01

请问现在想计算<下游负责人>列内:朱五,   <下游企业类型> 列内项目为"门店"、“零售终端”、"医院"、"基层医疗机构",  <批号>列内批号为：210302、210511、210805的销售数量，该怎么写公式？头都搞晕了
大写的谢谢！

高个子

1. =SUM(SUMIFS(I:I,B:B,"朱五", C:C,{"门店","零售终端","医院","基层医疗机构"},E:E,{"210302";"210511";"210805"}))

复制代码

良良

=SUM(SUMIFS(I:I,B:B,"朱五",C:C,{"门店","零售终端","医院","基层医疗机构"},E:E,210302)+SUMIFS(I:I,B:B,"朱五",C:C,{"门店","零售终端","医院","基层医疗机构"},E:E,210511)+SUMIFS(I:I,B:B,"朱五",C:C,{"门店","零售终端","医院","基层医疗机构"},E:E,210805))

良良

简化一下：
=SUM(SUMIFS(I:I,B:B,{"朱五","朱五","朱五","朱五"},C:C,{"门店","零售终端","医院","基层医疗机构"},E:E,{210302;210511;210805}))

博爱风雨同舟

=SUMPRODUCT(($B$2:$B$605="朱五")*(MMULT(--($C$2:$C$605={"门店","零售终端","医院","基层医疗机构"}),ROW($1:$4)^0))*(MMULT(--($E$2:$E$605={210302,210511,210805}),ROW($1:$3)^0))*($I$2:$I$605))

wshj01

尝试着写了个公式：=SUM(SUMIFS(I:I,B:B,"朱五",C:C,{"门店","零售终端","基层医疗机构","医院"},E:E,{210302,210511,210805}))公式不报错，但返回的结果却不对，请哪位大虾指导下，谢谢！

博爱风雨同舟

=SUMPRODUCT(($B$2:$B$605="朱五")*(MMULT(--($C$2:$C$605={"门店","零售终端","医院","基层医疗机构"}),ROW($1:$4)^0))*(MMULT(--($E$2:$E$605={210302,210511,210805}),ROW($1:$3)^0))*($I$2:$I$605))

cjyjimmy

=SUMPRODUCT((B:B="朱五")*((C:C="门店")+(C:C="零售终端")+(C:C="医院")+(C:C="基础医疗机构"))*((E:E=210302)+(E:E=210511)+(E:E=210805)))

cjyjimmy

=SUMPRODUCT((B:B="朱五")*((C:C="门店")+(C:C="零售终端")+(C:C="医院")+(C:C="基础医疗机构"))*((E:E=210302)+(E:E=210511)+(E:E=210805)))

浮生若梦~~~

=SUMPRODUCT((B2:B605="朱五")*MMULT(N(C2:C605={"门店","零售终端","医院","基层医疗机构"}),{1;1;1;1})*(E2:E605={210302,210511,210805})*I2:I605)