Using SUMIFS & LARGE (or similar?)

Hello,

I have a sheet with 3 columns filled in:
A is "Veggies" in (text) with 3 items; carrots, cucumbers & peppers
B is the "value" I want to sum (numbers) (the column is long & updated weekly so I need the formula to be dynamic)
C is the "date" in (week numbers) (same as above)

I am looking to have 2 calculations here:
- The sum of the 5 largest "values" provided they are "carrots" (then, the sum of the top 15, then top 40)
- The sum of the 5 (then 15, 40) largest "values" provided they are "carrots" for a period of time inferior to X numbers of weeks ("date").

I've been browsing tons of forums but can't figure out how to do.
Note that I will also need to divide this value by the same formula from another sheet in a later step.

Any help is appreciated!

Thanks a lot

Attach sample file

All you describe can easily be done with pivot tables.

If you can upload a sample file, we can show you how.

Great. This is a good sample file. Now some questions.

- The sum of the 5 largest "values" provided they are "carrots"

5 largest (carrot) values as per <what>? Can you mock up the expected result?
then, the sum of the top 15, then top 40 -- As above, top 15 of what? These will need to be different pivot tables to sum different settings. Mock up the correct result for your data sample, so we know what to aim for.

- The sum of the 5 (then 15, 40) largest "values" provided they are "carrots" for a period of time inferior to X numbers of weeks ("date"). -- Again, mock up the desired result. Which of the data rows qualify? Why?

While I said that this can be done with Pivot Tables, I still need the exact business logic to apply the correct filter.

"the 5 (then 15, 40) largest " Such a sort is not possible within a single pivot table. You need separate pivots to do that.

As soon as you can mock up some results based on your data and your logic, I'll try to build it with a pivot table.

What you describe in your last comment can be done with the auto-filter, without involving pivot tables.

1. Filter column B to show only "carrots". Then filter column C > Number Filters > Top 10 > select the number of items you want to show.

2. Filter on colun B to show only "carrots". Then filter on column A "Date" > Date Filters > Between (set start and end date). Then filter by column C > Number filters > top 10 > select number of items.

You should turn the table into an Excel Table: Click any cell in the table and select Insert > Table. In Excel 2013 you can use slicers on simple data tables, so you can create a slicer for the veggie category and another slicer for the date range, or for the week. Then turn on the Total row for the table. Select the Veggie. Shift click the Week slicer for one or more weeks. Scroll down to see the total.

2014-12-08_21-19-23.png

cheers, teylyn

See attached file