Reputation: 23
Sum a group of products using SUMIFS
I have a question I have tried function SUM and SUMIFS, It doesn't seem to work for me I am not sure what I missed in the formula.
Here, I want to sum a group of product in column B which match the specific date in column D. I tried
=SUM(SUMIFS($C:$C,$B:$B,{"1","4","6"},$A:$A,$D1))
the result turned to be 12, but the correct result should be 16. If I separated it into three function >
=SUMIFS($C:$C,$B:$B,"1",$A:$A,$D1)+SUMIFS($C:$C,$B:$B,"4",$A:$A,$D1)+SUMIFS($C:$C,$B:$B,"6",$A:$A,$D1)
It works fine. but I just want to know if I can actually group it or make it shorter because I have a lot of product to be grouped.
Upvotes: 0
Views: 73
Answers (1)
Reputation: 921
For some reason that I can't figure out, I was not able to get it to work using ARRAYFORMULA and SUMIFS.
But this formula with nested IF works fine and also takes care of additional products in E.
=ARRAYFORMULA(SUM((IF(SPLIT($G2, ",")=$B:$B, IF($A:$A=$F2, $C:$C, 0), 0))))
Upvotes: 2