• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Formulas / Find the 2nd Largest Value Using Multiple Criteria

Find the 2nd Largest Value Using Multiple Criteria

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.

Related Posts:

  • Lookup Multiple Values in Excel
    Lookup Multiple Values in Excel
  • Excel Formula to Find the Cell Address of a Value
    Excel Formula to Find the Cell Address of a Value
  • Multiple Condition Lookup Formula in Excel
    Multiple Condition Lookup Formula in Excel
  • Import Multiple Excel Files with Multiple Sheets in Excel
    Import Multiple Excel Files with Multiple Sheets in Excel

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • IF Function in Power Query Including Nested IFS
  • Conditional Formatting Multiple Columns – 3 Examples

Recent Posts

  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
  • Display Negative Time in Excel
  • Lookup Multiple Values in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·