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
[TABLE="width: 128"]
<tbody>[TR]
[TD]Player[/TD]
[TD="width: 64"]Handicap[/TD]
[/TR]
[TR]
[TD="class: xl65"]Brett[/TD]
[TD="align: right"]12.35961[/TD]
[/TR]
[TR]
[TD="class: xl65"]Sam[/TD]
[TD="align: right"]8.802885[/TD]
[/TR]
[TR]
[TD="class: xl65"]Srihdar[/TD]
[TD="align: right"]4.356984[/TD]
[/TR]
[TR]
[TD="class: xl65"]Vivek[/TD]
[TD="align: right"]3.467803[/TD]
[/TR]
[TR]
[TD="class: xl65"]Brett[/TD]
[TD="align: right"]7.024525[/TD]
[/TR]
[TR]
[TD="class: xl65"]Sam[/TD]
[TD="align: right"]8.802885[/TD]
[/TR]
[TR]
[TD="class: xl65"]Srihdar[/TD]
[TD="align: right"]4.356984[/TD]
[/TR]
[TR]
[TD="class: xl65"]Vivek[/TD]
[TD="align: right"]3.467803[/TD]
[/TR]
[TR]
[TD="class: xl65"]Brett[/TD]
[TD="align: right"]8.802885[/TD]
[/TR]
[TR]
[TD="class: xl65"]Sam[/TD]
[TD="align: right"]8.802885[/TD]
[/TR]
[TR]
[TD="class: xl65"]Srihdar[/TD]
[TD="align: right"]4.356984[/TD]
[/TR]
[TR]
[TD="class: xl65"]Vivek[/TD]
[TD="align: right"]3.467803[/TD]
[/TR]
[TR]
[TD="class: xl65"]Brett[/TD]
[TD="align: right"]5.246164[/TD]
[/TR]
[TR]
[TD="class: xl65"]Sam[/TD]
[TD="align: right"]7.858394[/TD]
[/TR]
[TR]
[TD="class: xl65"]Srihdar[/TD]
[TD="align: right"]3.587528[/TD]
[/TR]
[TR]
[TD="class: xl65"]Vivek[/TD]
[TD="align: right"]2.733354[/TD]
[/TR]
[TR]
[TD="class: xl65"]Brett[/TD]
[TD="align: right"]9.56674[/TD]
[/TR]
[TR]
[TD="class: xl65"]Sam[/TD]
[TD="align: right"]7.858394[/TD]
[/TR]
[TR]
[TD="class: xl65"]Srihdar[/TD]
[TD="align: right"]3.587528[/TD]
[/TR]
[TR]
[TD="class: xl65"]Vivek[/TD]
[TD="align: right"]2.733354[/TD]
[/TR]
[TR]
[TD="class: xl65"]Brett[/TD]
[TD="align: right"]7.173677[/TD]
[/TR]
[TR]
[TD="class: xl65"]Sam[/TD]
[TD="align: right"]6.233552[/TD]
[/TR]
[TR]
[TD="class: xl65"]Srihdar[/TD]
[TD="align: right"]2.185791[/TD]
[/TR]
[TR]
[TD="class: xl65"]Vivek[/TD]
[TD="align: right"]1.376239[/TD]
[/TR]
[TR]
[TD="class: xl65"]Brett[/TD]
[TD="align: right"]7.043104[/TD]
[/TR]
[TR]
[TD="class: xl65"]Sam[/TD]
[TD="align: right"]6.233552[/TD]
[/TR]
[TR]
[TD="class: xl65"]Srihdar[/TD]
[TD="align: right"]2.185791[/TD]
[/TR]
[TR]
[TD="class: xl65"]Vivek[/TD]
[TD="align: right"]1.376239

[/TD]
[/TR]
</tbody>[/TABLE]

 

[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}))}