Extract 9 largest categories - similar to pivot table but need formula

Hello,

What I'm looking to do is sum the total of the 9 largest categories and then have the 10th category as "other" which will sum the total of all others. I'm able to extract the top 9 and other - but if the category has two rows, I need to figure out how to sum it, then extract it. Thank you so much!!

Sum categories extract to overview tab.xlsx

Used helper in C4 down to rank duplicates

=B4+ROWS($1:1)*10^-3

In G4 to G12

=LARGE($B$4:$B$14,ROWS($1:1))

in G13

=SUMIF($C$4:$C$14,"<" &LARGE($C$4:$C$14,9),$B$4:$B$14)

Thanks, John!

The only issue with this is the data is always going to change and I basically need the data extracted over from the data tab. Unless I placed the helper in C4 in the wrong place... I placed it next to the pivot table which already had those numbers there. How do you get the categories to sum all of the rows and then extract the top 9 without using a pivot table? Thanks so much!!

See attached:

In E4 and copy down to get unique list of "fruits"

=IFERROR(INDEX(Table1[type],MATCH(0,INDEX(COUNTIF($E$3:E3,Table1[type]),0,0),0)),"")

in F4 total values

=SUMIF(Table1[type],Overview!E4,Table1[size])

in G4 Ranking

=RANK(F4,$F$4:$F$14)+ROWS($1:1)*10^-3

In I4/J4 sorted list

=INDEX($E$4:$E$14,MATCH(SMALL($G$4:$G$14,ROWS($1:1)),$G$4:$G$14,0))

=INDEX($F$4:$F$14,MATCH(SMALL($G$4:$G$14,ROWS($1:1)),$G$4:$G$14,0))

in L4 down for 9 rows

=INDEX($I$4:$I$14,MATCH(LARGE($J$4:$J$14,ROWS($1:1)),$J$4:$J$14,0))

In M4

=LARGE($J$4:$J$14,ROWS($1:1))

in M13

=SUMIF($G$4:$G$14,">" &SMALL($G$4:$G$14,9),$J$4:$J$14)

Note change to SMALL!

This is perfect! Thank you so much!

One other question Could you do the same thing but instead only pull the data that has the letter "a" in column B on the data tab?

The simplest way is to change the SUMIF in column F to SUMIFS

=SUMIFS(Table1[size],Table1[type],Overview!E4,Table1[letter],"a")

You will get zeros for many of the values.

Totally makes sense! Is there a way to add it to the type category? Thank you so much!

In what way: do you want "Strawberry-a" or similar?

I only want to find the top values (in this case it's 3 or so) that have the letter 'a' in the letter column. So it would only sum the total of strawberry, apple, and cranberry. You're awesome - thanks so much!

That's what the SUMIFS does: simply sums any "a" products.

Perfect - thanks, Tom!

Okay - last question!

When the data is extracted over to the "sorted by rank" table - how would you not include the category "carrot" - it just skips it?