SUM n smallest

[jotari]

Array formula {=SUM(LARGE(($B$2:$B$50)*($A$2:$A$50=D2),{1,2,3}))} works fine, but if I try to find SUM of 3 smallest, formula doesn't work. Why?
{=SUM(SMALL(($B$2:$B$50)*($A$2:$A$50=D2),{1,2,3}))}

 

[shg]

The ($A$2:$A$50=D2) term makes a lot of the values zero, right?

Try

{=IF(COUNTIF($A$2:$A$50, D2) < 3, "Oops", SUM(SMALL(IF($A$2:$A$50=D2, $B$2:$B$50, 9E+307),{1,2,3})))}

 

[Aladin Akyurek]

Array formula {=SUM(LARGE(($B$2:$B$50)*($A$2:$A$50=D2),{1,2,3}))} works fine, but if I try to find SUM of 3 smallest, formula doesn't work. Why?
{=SUM(SMALL(($B$2:$B$50)*($A$2:$A$50=D2),{1,2,3}))}

Better avoid pairwise multiplications. Invoke IF for filtering instead...

Control+shift+enter, not just enter:

=SUM(SMALL(IF(A2:A50=D2,B2:B50),{1,2,3}))

 

[jotari]

Thank You. Works fine

 

[shg]

=SUM(SMALL(IF(A2:A50=D2,B2:B50),{1,2,3}))

I thought mine looked unnecessarily complicated ...

 

[jotari]

One more question. Formula =SUM(SMALL(IF(A2:A50=D2,B2:B50),{1,2,3})) does not work if there is only 2 or 1 equal (=D2) number in column A.
What is the formula if I want anyway count those 2 together or just only one.

 

[Aladin Akyurek]

One more question. Formula =SUM(SMALL(IF(A2:A50=D2,B2:B50),{1,2,3})) does not work if there is only 2 or 1 equal (=D2) number in column A.
What is the formula if I want anyway count those 2 together or just only one.

Control+shift+enter, not just enter:

=SUM(SMALL(IF(A2:A50=D2,B2:B50),ROW(INDIRECT("1:"&MIN(3,COUNTIF(A2:A50,D2))))))