Sumif and Small functions help

[Bengmark]

I'm struggling with what I think should be a simple function. Hoping someone can help.

I want to use a Sumif focusing on the name. i.e. =SUMIF(B2:B29,"Brett",H2:H29). This equals 57.2
But I only want to smallest 3 numbers. i.e. =SUM(SMALL(H2:H29,{1,2,3})). This equals 1.64

So I thought I would be able to do this. =SUMIF(B2:B29,"Brett",SMALL(H2:H29,{1,2,3}))

But I get an error. I've tried using Sumifs as well. Same result. Shouldn't I be able to embed the small command in the sum range? I'm using Excel 2013.


B H

Player	Handicap
Brett	12.35961
Sam	8.802885
Srihdar	4.356984
Vivek	3.467803
Brett	7.024525
Sam	8.802885
Srihdar	4.356984
Vivek	3.467803
Brett	8.802885
Sam	8.802885
Srihdar	4.356984
Vivek	3.467803
Brett	5.246164
Sam	7.858394
Srihdar	3.587528
Vivek	2.733354
Brett	9.56674
Sam	7.858394
Srihdar	3.587528
Vivek	2.733354
Brett	7.173677
Sam	6.233552
Srihdar	2.185791
Vivek	1.376239
Brett	7.043104
Sam	6.233552
Srihdar	2.185791
Vivek	1.376239

<tbody>
</tbody>

 

[Ron Coderre]

Nope. SUMIF(S) doesn't work like that.
Try this regular formula:

=SUMPRODUCT(SMALL((B2:B29="Brett")*H2:H29,{1,2,3}+COUNTIF(B2:B29,"<>Brett")))

Is that something you can work with?

 

[Bengmark]

Thanks Ron. This looks like it works exactly like I need. I had not used the SUMproduct function before.

Thanks again.
Brett

 

[Peter_SSs]

This looks like it works exactly like I need.

Welcome to the MrExcel board!

I guess with a heading like "Handicap" it may not be possible with your data, but that formula would fail if it was possible that Brett could have any negative values in column H.

If you are interested, I think this formula also does what you want (& would also cope if negatives can exist).
It is an array formula so should be entered without the surrounding {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}

{=SUM(SMALL(IF(B2:B29="Brett",H2:H29,""),{1,2,3}))}