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:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

 

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

Two Way Lookup Using The SUM Intersect Function There are various lookup functions that bring back values from a range of data like the VLOOKUP, INDEX, INDEX-MATCH and OFFSET functions, just to name a few. There is another lookup formula that you can use that will return the intersection of two Named Ranges! This is done...
Highlight All Excel Formula Cells   Whenever you are auditing an Excel worksheet and need to know where all the formulas are located, a great way is to highlight the formula cells in a distinctive color.  This is how it is done: STEP 1: Select all the cells in your Excel worksheet by clicking on the to...
Advanced SUMPRODUCT Function: Conditional Sum Another great way that you can use the SUMPRODUCT function is to create a conditional sum criteria. For example, you may want to find out how much sales were made above the $3,000 transactional level.  See how easy it is with this quick SUMPRODUCT example. DOWNLOAD WORKBOOK...
How to Convert Formulas to Values   Have you ever had a scenario where you write a formula and just want to show the value output only and get rid of the formula? Here is an example of a formula:   Well I do not need the formula, bit I do want the last names only....hard copied! Fortu...