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


Advanced Sumproduct_Top 3 Sales


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


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Share on 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...