Thread: sum with multi conditions

Hi!
I have the followin data.

A B C D E F g
SB No KW Mot_No FrB_No qty rea_br Qty
1 0.37 2 6202 2 6202 2
1 0.75 6 6204 6 6203 6
1 1.1 11 6205 11 6205 11
2 2.2 3 6306 3 6206 3
2 5.5 3 6307 3 6207 3
2 18.5 4 6310 4 6210 4
1 22 8 6310 8 6308 8
1 30 2 6213 2 6211 2
3 37 1 6312 1 6212 1
3 55 1 Nu217 1 6217 1
3 75 3 6316 3 6316 3
1 300 3 6322 3 6322 3

i need that if enter bR_No,SBNo then it return qty form both column of qty where Br= 6202. Where 6202 exist in both column"D" & "F" and qty in column "E" & "H"

BR_No SB NO Qty expected result
6202 1 ? 4
6213 3 ? 2
6322 1 ? 6

Thanks and regards

Not sure I understand from your source data how you get 2 for 6213/3 given there are no instances of SB No = 3 and D/F being 6213 ?

If we assume you put 6202 into H2 and 1 into I2 then to get total in J2 (given your use of XL2007)

=SUMIFS(E:E,D:D,H2,A:A,I2)+SUMIFS(G:G,F:F,H2,A:A,I2)

For backwards compatible version you're looking at SUMPRODUCT which will be a little less efficient:

=SUMPRODUCT((($A$2:$A$100=$I2)*($D$2:$D$100=$H2)*($E$2:$E$100))+(($A$2:$A$100=$I2)*($F$2:$F$100=$H2)*($G$2:$G$100)))

note with sumproduct it is imperative you keep ranges to a minimum.

Hi D.K!

Please see the attach filr this may explain a bit.

Note: Excel Jeanie does not work on this particular forum - please attach a file directly rather than screenshot.

On an aside, if you have posted this question on other forums please ensure you provide links.

test(1).xls



Also, more usefull is to write in expected result rather than Q(?)

Based on your initial post I suspect:

B22:
=SUMIFS($F$2:$F$18,$E$2:$E$18,$A22,$A$2:$A$18,C$21)+SUMIFS($H$2:$H$18,$G$2:$G$18,$A22,$A$2:$A$18,C$21)
applied across matrix

DQ formula, just range was moved for one cell:

Just copy in B22 and extend right and down

=SUMIFS($F$2:$F$18;$E$2:$E$18;$A22;$A$2:$A$18;B$21)+SUMIFS($H$2:$H$18;$G$2:$G$18;$A22;$A$2:$A$18;B$21)

or with ,

=SUMIFS($F$2:$F$18,$E$2:$E$18,$A22,$A$2:$A$18,B$21)+SUMIFS($H$2:$H$18,$G$2:$G$18,$A22,$A$2:$A$18,B$2 1)