Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to get first column number in range in Excel
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Left Lookup in Excel
  • Count unique text values with criteria
  • Two-way lookup with VLOOKUP in Excel

Data Analysis Examples

  • Subtotal function in Excel
  • Chart Axes in Excel
  • Use Data Form to input, edit and delete records in Excel
  • How to create a Histogram in Excel
  • How to add Trendline to a chart in Excel

Data Validation Examples

  • Excel Data validation allow uppercase only
  • Excel Data validation no punctuation
  • Excel Data validation exists in list
  • Excel Data validation whole percentage only
  • Excel Data validation don’t exceed total

Sum bottom n values with criteria in Excel

by

This tutorial shows how to Sum bottom n values with criteria in Excel.

You can use a combination of SUM function, SMALL function and IF function to get the sum bottom in the example below;

Formula

{=SUM(SMALL(IF(range1=criteria,range2),{1,2,3,N}))}

Explanation

To sum the bottom n values in a range matching criteria, you can use an array formula based on the SMALL function, wrapped inside the SUM function. In the generic form of the formula (above), range1 represents the range of cells compared to criteria, range2 contains numeric values from which bottom values are retrieved, and N represents “nth”.

Worked Example:   Nested IF function example in Excel

In the example, the active cell contains this formula:

=SUM(SMALL(IF(color=E5,value),{1,2,3}))

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

Note: this is an array formula and must be entered with control + shift + enter.

Here’s how the formula works

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

=SMALL (range,N)

So, for example:

=SMALL (C5:C12,2)

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

Worked Example:   Calculate total hours that fall between two times in Excel

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

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

will return the 1st, 2nd, and 3rd smallest value C5:C12 in an array like this: {4,5,7}.

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

IF(color=E5,value)

This builds the array of values fed into SMALL. Essentially, only values associated with the color red make it into the array. Where color equals “red”, the array contains a number, and where the color is not red, the array contains FALSE:

SMALL({12;FALSE;10;FALSE;8;4;FALSE;FALSE},{1,2,3}))

The SMALL function ignores the FALSE values and returns the 3 smallest values in the array: {4,8,10}. The SUM function returns the final result, 22.

Worked Example:   Calculate series of dates by workdays in Excel

Post navigation

Previous Post:

Sum by month ignore year in Excel

Next Post:

Sum bottom n values 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
  • How to Check If A Cell Contains Specific Text in Excel
  • DBCS function: Description, Usage, Syntax, Examples and Explanation
  • Remove file extension from filename in Excel
  • How to extract name from email address in Excel
  • How to pad text to match equal length in Excel
  • Convert Excel time to Unix time in Excel
  • Next biweekly payday from date in Excel
  • DAYS function: Description, Usage, Syntax, Examples and Explanation
  • Count birthdays by month in Excel
  • How to calculate next day of week in Excel
  • Compound Annual Growth Rate CAGR formula examples in Excel
  • YIELDDISC function: Description, Usage, Syntax, Examples and Explanation
  • Calculate cumulative loan principal payments in Excel
  • How to calculate compound interest in Excel
  • SYD function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles