SUMIF to return only first two matches

[inzaghi]

I am looking for a SUMIF that returns only the first two matches, not the results from the full table. In the example below, I would like to return a SUM of 5 (not 6), please.

Bacon.PNG

[Glenn Kennedy]

How is this:
=SUMIFS($C$4:$C$100,$B$4:$B$100,F3,$C$4:$C$100,"<="&AGGREGATE(15,6,$C$4:$C$100/($B$4:$B$100=F3),2))

[inzaghi]

It's close - collecting the right data, but what it has done has picked up the last match, then the first match (instead of the first two matches)

If I change the last criteria in the formula to 3 instead of 2, it then picks up the last, first & second matches.

[inzaghi]

Actually, I can see that it is finding the smallest numerical match + the next smallest when the formula restricts it to 2.

Rather than the smallest 2 numerical matches, I am simply looking to find the first 2 matches as they appear in the list (regardless of value).

[menem]

(Sorry about my English)

I just can do this.

Put a value you want to sum as "Bacon" in E3.
And then use this formula

=IFERROR(INDIRECT("C"&AGGREGATE(15,6,ROW($B$4:$B$14)/(1/($B$4:$B$14=$E$3)),1)),0)+
IFERROR(INDIRECT("C"&AGGREGATE(15,6,ROW($B$4:$B$14)/(1/($B$4:$B$14=$E$3)),2)),0)


Sorry , this may not help much.

Regards.

[Glenn Kennedy]

Inzaghi, you have me baffled. As afar as I can see it's fine. Unfortunately, you didn't post a file showing it calculating the wrong result, to I can only guess that you have made an error.

See file, with a modified choice of values, and an unchanged formula. Show me EXACTLY what is wrong.

[Glenn Kennedy]

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[inzaghi]

Glenn,

If you change the "6" in C8 to "150" (above the value of the next biggest entry for "W"), then the new value returned becomes 109 (i.e. the SUM of the two smallest matches). I'd want the value returned to be 159 (i.e. the SUM of the first two matches for W). See attached.

[kvsrinivasamurthy]

This works
ARRAY formula

=IFERROR(SUM((IFERROR(C4:C16*(IF(B4:B16=F3,ROW(B4:B16),"")<=SMALL(IF(B4:B16=F3,ROW(B4:B16),""),2)),0))),"")

How ARRAY formula is entered

Paste Formula in the cell.
Press F2
Hold Shift+ Ctrl Keys and hit Enter key.
Now the formula is surrounded by {} by excel.

[Glenn Kennedy]

Well, ain't that a pain in the butt!! I was certain it was OK.

Use this ordinary formula:

=SUM(INDEX(($C$4:$C$16)*($B$4:$B$16=F3)*(ROW($4:$16)<=SMALL(INDEX(ROW($4:$16)+($B$4:$B$16<>F3)*1E+99,,), 2)),,))

Do your best to break it!!

[inzaghi]

Glenn, you've cracked it! Had to tweak/simplify my data table to make it work, but its singing beautifully now. Spent hours trying to figure it out myself, until eventually, at the age of 39, admitted defeat and asked for help! Very happy you've fixed this for me, many thanks!

[Glenn Kennedy]

Great. Glad to have sorted you out. Sadly, I'll not see 39 again, but never afraid to admit defeat!!

[XLent]

FWIW, re: AGGREGATE method:

=SUM(MOD(AGGREGATE(15,6,(ROW($C$4:$C$16)*10^7+$C$4:$C$16)/($B$4:$B$16=$F$3),{1,2}),10^7))

[inzaghi]

Well, ain't that a pain in the butt!! I was certain it was OK.

Use this ordinary formula:

=SUM(INDEX(($C$4:$C$16)*($B$4:$B$16=F3)*(ROW($4:$16)<=SMALL(INDEX(ROW($4:$16)+($B$4:$B$16<>F3)*1E+99,,), 2)),,))

Do your best to break it!!

Glenn, building on the quoted fix, if I wanted to apply the same formula to 2 potential columns (SUMIFS or SUMIF(OR() I presume?), how would the formula change? In the attached example for the first 3 instances of 'E' I'd want to return an sum of 612.

[NARAYANK991]

Hi ,

Using helper columns makes it simple.

Use one helper column with the formula :

Please Login or Register  to view this content.

. Enter this in L4 and copy down.

Use a second helper column with the formula :

Please Login or Register  to view this content.

. Enter this in M4 and copy it down.

The formula in cell I4 is :

Please Login or Register  to view this content.

. This is an array formula , to be entered using CTRL SHIFT ENTER.

Narayan

[XLent]

...if I wanted to apply the same formula to 2 potential columns (SUMIFS or SUMIF(OR() I presume?), how would the formula change? In the attached example for the first 3 instances of 'E' I'd want to return an sum of 612.

with the AGGREGATE approach:

Please Login or Register  to view this content.

[inzaghi]

Formula works perfectly, thank-you very much! In addition, would there be a way to make it work if the data wasn't in the subsequent column to the reference (E)? Example attached, where again I'd be looking to return a sum of 612.

[XLent]

using that specific sample file layout:

Please Login or Register  to view this content.

[inzaghi]

Excellent, again that works perfectly, thank-you.

Just one final thing, if I then wanted to sum all instances, rather than just the first 3, what would I take out of the formula?

[JeteMc]

Try pasting the following into cell N3: =SUM(SUMIFS(J$4:J$16,B$4:B$16,M3),SUMIFS(K$4:K$16,E$4:E$16,M3))
Let us know if you have any questions.

[inzaghi]

Awesome, thank you JeteMc, that works perfectly!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.