Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Conditional Formatting New Rule with Formulas in Excel
  • How to count table columns in Excel
  • Randomize/ Shuffle List in Excel
  • How to calculate correlation coefficient Correlation in Excel
  • Subtotal function in Excel

References

  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • Last row number in range
  • Left Lookup in Excel
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • Find closest match in Excel

Data Validations

  • Excel Data validation must begin with
  • Excel Data validation whole percentage only
  • Excel Data validation allow uppercase only
  • Excel Data validation with conditional list
  • Excel Data validation number multiple 100

Sum top n values with criteria in Excel

by

This tutorial shows how to Sum top n values with criteria in Excel using the example below;

Formula

=SUMPRODUCT(LARGE((range=criteria)*(values),{1,2,3,N}))

Explanation

To sum the top n values in a range matching criteria, you can use a formula based on the LARGE function, wrapped inside the SUMPRODUCT function. In the generic form of the formula (above), range represents a range of cells that are compared to criteria, values represents numeric values from which top values are retrieved, and N represents the idea of Nth value.

In the example, the active cell contains this formula:

=SUMPRODUCT(LARGE((color=E5)*(value),{1,2,3}))

Where color is the named range B5:B12 and value is the named range C5:C12.

Here’s how the formula works

In its simplest form, LARGE returns the “Nth largest” value in a range with this construction:

=LARGE (range,N)

So, for example:

=LARGE (C5:C12,2)

will return the 2nd largest value in the range C5:C12, which is 12 in the example shown.

However, if you supply an “array constant” (e.g. a constant in the form {1,2,3}) to LARGE as the second argument, LARGE will return an array of results instead of a single result. So, the formula:

=LARGE (C5:C12, {1,2,3})

will return the 1st, 2nd, and 3rd largest value C5:C12 in an array like this:{12,12,10}

So, the trick here is to filter the values based on color before LARGE runs. We do this with the expression:

(color=E5)

Which results in an array of TRUE / FALSE values. During the multiplication operation, these values are coerced into ones and zeros:

=LARGE({1;0;1;0;1;1;0;0}*{12;12;10;9;8;8;7;5},{1,2,3})

So the final result is that only values associated with the color “red” survive the operation:

=SUMPRODUCT(LARGE({12;0;10;0;8;8;0;0},{1,2,3}))

and the other values are forced to zero.

Note: this formula won’t handle text in the value range. See below.

Handling text in values

If you have text anywhere in the value ranges, the LARGE function will throw a #VALUE error and stop the formula from working.

To handle text in the value range, you can add the IFERROR function like this:

=SUM(IFERROR(LARGE(IF((color=E5),value),{1,2,3}),0))

Here, we trap errors from LARGE caused by text values and replace with zero. Using IF inside LARGE requires that the formula be entered with control + shift + enter, so we switch to SUM instead of SUMPRODUCT.

Post navigation

Previous Post:

How to use Excel CHOOSE Function

Next Post:

Customize Ribbon In Excel

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • How to use Excel TRUE Function
  • Excel If, Nested If, And/Or Criteria Examples
  • IF with boolean logic in Excel
  • How to use Excel FALSE Function
  • Extract multiple matches into separate rows in Excel

Date Time

  • Get work hours between dates and times in Excel
  • MINUTE function: Description, Usage, Syntax, Examples and Explanation
  • Get days, months, and years between dates in Excel
  • Generate series of dates by weekends in Excel
  • Count dates in current month in Excel

Grouping

  • Categorize text with keywords in Excel
  • Group numbers at uneven intervals in Excel
  • Group times into unequal buckets in Excel
  • Group numbers with VLOOKUP in Excel
  • Map inputs to arbitrary values in Excel

General

  • Basic text sort formula in Excel
  • Lock Cells in a Worksheet Excel
  • How to Insert Cells, Row and Rows in Excel
  • How to Delete Cells, Row and Rows in Excel
  • How to get original price from percentage discount in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning