Download Excel Workbook
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 workbookAdvanced-Sumproduct_Top-3-Sales-with-an-Array-Formula.xlsx
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.
