Summing Largest 50 values based on criteria

Hello Hello.

I have been working on this for 2 days and it's now time for more brains:

I have an excel sheet of 8000 products (rows) from a supermarket. Column A are product names (The Economist, New York Times, Snickers, Time Magazine, etc) Column B has each product broken down by % of category sales (the economist is 3.9% of magazine sales, People is 2.9% of magazine sales, etc) and Column C is the category (Newspapers, magazines, candy, beverage, etc). I'm attempting to make a separate chart that sums the percent of sales of:

the top 5 highest grossing magazines (I want to know the top 5 magazines represent __% of my total magazine sales)
the top 10 highest grossing magazines
the top 15 highest grossing magazines
all the way up to the top 50 magazines

I'll be doing this for about 20 different categories, and I'm hoping some simple formulas would do the trick. My problem is (well, at least one of my problems!) that I don't know how to combine a sumif (or sumifs) function while also plugging in the criteria of adding up just the top 5 (or top 50) highest values (percentages) of ONLY magazines (and then ONLY Newspapers, etc). Or, perhaps I shouldn't be trying to combine these formulas. Also, this worksheet will constantly be sorted in different ways, so the formulas have to be able to handle unsorted columns. Any ideas????

Many thanks!

~ Scooby

I thought you could add a "rank" column using formula: =COUNTIFS(C:C,C2,B:B,">"&B2)+1 copying down from D2

Then you could have a sumifs formula with the criteria in two cells or hard coded.

See the attached spreadsheet. shop items.xlsx

Hello TMS,

Thanks for your help.
If I'm understanding this right, Your formula will look up the Top 5 from the entire Column A, however, I'm looking for the Top 5 from Column B, and ONLY from the category of magazines.

Thanks!

Cerbera, that's more of what I'm looking for. Thanks. Is there a way to do this without adding a column?

Mr. Valko, thanks! I'll give this a go soon. I already implemented Cerbera's suggestion and added an extra few columns as helper columns and this works wonderfully. However, I ran into a snag:

I'm looking for the top items that scan through our registers. Sometimes, a barcode isn't in our system yet and the price needs to be manually entered at the register and put into a general category. For manually inputted candy at the register, I've labelled it *CANDY in our register system. Sometimes, this "item" shows up as a top seller, even though it represents many different items. And sometimes, it shows up as the second best selling item. I'd like to disregard this "item" in my formula.

Here is my formula:

=SUMIFS($M$28:$M$7000,$T$28:$T$7000,AC$3,$Y$28:$Y$7000,"<="&$AA5)

Column M is % of Sales I'm summing
Column T is the Category Number (each number represents a Category, for example: "Regular Candy" is Category 25
AC3 is 25 (for "Regular Candy")
Column Y is my helper column with Cerbera's COUNTIFS formula. Here is the formula taken from *CANDY:

=COUNTIFS($T$28:$T$7000,$T478,$M$28:$M$7000,">"&$M478)+1

The names of items are in Column F. Basically, what can I include in my SUMIFS formula to make it disregard an asterisk (*) at the very beginning of words from Column F and still sum the top 5 (or top 10, etc)? The asterisk is always the first character. Or maybe there's a better way??

My apologies about all this! I feel like there's always exceptions to rules and I need all the exceptions built into the formulas and it drives me mad!

Many Many Many Thanks. This forum has been extremely useful and this is my first post. I'm sorry I waited so long to do so!

~ Scooby

I probably should have attached this at the beginning!


Thank You!

Hello. I think I got it:

I used Cerbera's method, however I turned the COUNTIFS statement into an IF statement. Let's say the product names are in column A and since the items I didn't want in my calculations all contained an asterisk as the first character, I used: IF(LEFT(A1,1)<>"*",(COUNTIFS(.....cerbera's formula.....),9999) which puts this product at the bottom of the helper column's category.

I appreciate all the help. You guys are the best!

Much appreciated,

~ Scooby

Done and Done!

You guys rock. I'll be back for more soon.......

(this forum is slightly addicting.)

Hi Scooby, glad you get resolution to the problem.

If you feel that our input was useful can you click on the "add reputation" button below our details in the posts? I really need it as I'm trying to build some! Thanks