本帖最后由 ddjjguo 于 2021-10-26 11:06 编辑
g14单元格
=IFS($F14="A",SUMPRODUCT(($B$3:$B$11=$B14)*($E$3:$E$11=LEFT(G$13,1)),$F$3:$F$11),AND($F14="B",OR(LEFT(G$13,1)={"B","C"})),SUMPRODUCT(($B$3:$B$11=$B14)*($E$3:$E$11=LEFT(G$13,1)),$F$3:$F$11),AND($F14="C",LEFT(G$13,1)="C"),SUMPRODUCT(($B$3:$B$11=$B14)*($E$3:$E$11=LEFT(G$13,1)),$F$3:$F$11),1,0)
右拉下拉即可,感觉可以缩短,不过没想出来
简化了下
=IF(OR(LEFT(G$13,1)&$F14={"CC","CB","CA","BB","BA","AA"}),SUMPRODUCT(($B$3:$B$11=$B14)*($E$3:$E$11=LEFT(G$13,1)),$F$3:$F$11),0)
继续简化
=IF($F14<=LEFT(G$13,1),SUMPRODUCT(($B$3:$B$11=$B14)*($E$3:$E$11=LEFT(G$13,1)),$F$3:$F$11),0)
和楼下大神的想法一致了
|