An interesting question came up during one of my consultancy visits last week. They needed to find the top 5 values in a list based on multiple criteria. They then also needed the name of the company achieving that value.
Using the LARGE and SUMPRODUCT Functions
You may already be aware of the LARGE function. This function is used to return the nth largest value from a list.
To find the 1st and then 2nd, 3rd, 4th and 5th largest values in a list based on multiple criteria, we will need to use the LARGE function with the SUMPRODUCT function.
The SUMPRODUCT function is a brilliant and incredibly useful function for many situations. It is commonly used to test multiple criteria such as in this example.
The formula below demonstrates finding the 2nd largest value from a list where UK is in column B and London is in column C.
=SUMPRODUCT(LARGE((Data!$B$2:$B$6="UK")*(Data!$C$2:$C$6="London")*(Data!$E$2:$E$6),2))
Each criteria is placed in parenthesis and the multiply sign is used to ensure each criteria must be met. The plus sign could be used instead to apply OR logic between tests.
Being able to use formulas such as this to analyse data is extremely beneficial. Before we implemented this, they were using 3 PivotTables to drill down the information in sections.
Leave a Reply