EXCEL中使用SUMIFS进行条件筛选

Question

如题所述

Answer 1

把下面公式放到sheet1的B2单元格
=SUMIFS(Sheet2!B:B,Sheet2!A:A,"="&Sheet1!A2,Sheet2!C:C,"=A")
把下面公式放到sheet1的C2单元格
=SUMIFS(Sheet2!B:B,Sheet2!A:A,"="&Sheet1!A2,Sheet2!C:C,"=B")
把下面公式放到sheet1的D2单元格
=SUMIFS(Sheet2!B:B,Sheet2!A:A,"="&Sheet1!A2,Sheet2!C:C,"=C")+SUMIFS(Sheet2!B:B,Sheet2!A:A,"="&Sheet1!A2,Sheet2!C:C,"=D")
好了，搞定，拿分来吧！

Answer 2

实际上和sumif(条件,,,求和)差不多的,,两者不同的就是sumis先是求和列,条件1,,,条件2,,,
sumifs进行多条件求和_百度搜索
https://www.baidu.com/s?wd=sumifs%e8%bf%9b%e8%a1%8c%e5%a4%9a%e6%9d%a1%e4%bb%b6%e6%b1%82%e5%92%8c&tn=se_hldp00800_iw44c7tx&ie=utf-8&hl_tag=flayer
参考网址

Answer 3

B2
=SUMIFS(Sheet1!$B:$B,Sheet1!$A:$A,$A2,Sheet1!$C:$C,"A")
下拉
C列公式直接改最后参数A为B
D具公式，可用
=SUMIFS(Sheet1!$B:$B,Sheet1!$A:$A,$A2)-B2-C2