This is probably the most advanced level a SUMPRODUCT function can reach and that is by including a nested array formula.

In our example below we want to return the 3 Largest values from the North region and sum them up.  As we are asking our formula to perform multiple calculations i.e. Get the Largest 3 values or large((array,{1,2,3}), then an array formula is used.  So to make this formula work we need to finish it off by pressing CTRL+SHIFT+ENTER

DOWNLOAD WORKBOOK

Advanced Sumproduct_Top 3 Sales

Helpful Resource:

excel-formula-crash-course-from-chandoo.org

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

SUMIF Function: Introduction The SUMIF function is used widely amongst spreadsheet users as it is a simple Excel function.  It allows you to Sum the values in a range that meet a criteria that you specify. So if you want to Sum a range of sales values that are above $3,000 then this is the best Excel func...
OFFSET Function Introduced What does it do? It returns a reference to a range, from a starting point to a specified number of rows, columns, height and width of cells Formula breakdown: =OFFSET(reference, rows, columns, , ) What it means: =OFFSET(start in this cell, go up/down a number of ro...
Index Match 2 Criteria with Data Validation We can use the INDEX-MATCH formula and combine it with Data Validation drop down menus to return a value based on 2 criteria. This is a little advanced so you will need to drop what you are doing and really focus.  Let's go... First we need to convert our data into an Excel...
Sumproduct & Weighted Averages A quick way to calculate the weighted average of two lists of data is to use the Sumproduct formula.  A weighted average can be used to determine the average salary of employees, the average grade of an exam or the average selling price of a company´s stock list, as can been seen...