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

Data Analysis

  • How to Sort by Color in Excel
  • Add Outline to Data in Excel
  • Data Series in Excel
  • How to calculate current stock or inventory in Excel
  • Excel Pie Chart

References

  • How to calculate two-way lookup VLOOKUP in Excel Table
  • How to use Excel MMULT Function
  • Last row number in range
  • Perform case-sensitive Lookup in Excel
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation don’t exceed total
  • Excel Data validation must contain specific text
  • Excel Data validation unique values only
  • Excel Data validation number multiple 100
  • Excel Data validation date in next 30 days

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.

Worked Example:   Get work hours between dates custom schedule in Excel

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}

Worked Example:   Count cells over 100 characters in Excel

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.

Worked Example:   nth smallest value with criteria in Excel

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:

44 Practical Excel IF function Examples

Next Post:

Large with criteria 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 NOT Function
  • How to use Excel AND Function
  • How to use IFS function in Excel
  • SWITCH function example in Excel
  • How to use Excel TRUE Function

Date Time

  • Add months to date in Excel
  • Display Days in month in Excel
  • DAYS function: Description, Usage, Syntax, Examples and Explanation
  • Get last weekday in month in Excel
  • YEAR function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • How to randomly assign data to groups in Excel
  • How to randomly assign people to groups in Excel
  • Group times into 3 hour buckets in Excel
  • Categorize text with keywords in Excel
  • Group numbers with VLOOKUP in Excel

General

  • How to generate random times at specific intervals in Excel
  • Using Existing Templates in Excel
  • How to calculate percentage of total in Excel
  • How to increase by percentage in Excel
  • How to choose page/paper size in Excel before Printing
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning