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

Data Analysis

  • How to calculate correlation coefficient Correlation in Excel
  • Data Series in Excel
  • How to Sort by Color in Excel
  • How to create running total in an Excel Table
  • How To Remove Duplicates In Excel Column Or Row?

References

  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • How to retrieve first match between two ranges in Excel
  • Count rows that contain specific values in Excel
  • Create hyperlink with VLOOKUP in Excel
  • Approximate match with multiple criteria in Excel

Data Validations

  • Excel Data validation whole percentage only
  • Excel Data validation date in specific year
  • Excel Data validation must begin with
  • Excel Data validation exists in list
  • Excel Data validation require unique number

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:   Excel If, Nested If, And/Or Criteria Examples

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:   How to get last row in mixed data with blanks 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:   How to set check register balance in Excel

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 IFS function in Excel
  • Check multiple cells are equal in Excel
  • OR function: Description, Usage, Syntax, Examples and Explanation
  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • NOT function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • How to get same date next month or previous month in Excel
  • Get fiscal year from date in Excel
  • NOW function: Description, Usage, Syntax, Examples and Explanation
  • Add days to date in Excel
  • How to enter Today’s Date or Static Date and Time in Excel

Grouping

  • Group numbers at uneven intervals in Excel
  • If cell contains one of many things in Excel
  • How to randomly assign people to groups in Excel
  • Group numbers with VLOOKUP in Excel
  • Group times into unequal buckets in Excel

General

  • How to fill cell ranges with random text values in Excel
  • How to generate random number weighted probability in Excel
  • How to create dynamic named range with INDEX in Excel
  • Cell References: Relative, Absolute and Mixed Referencing Examples
  • How to calculate percent variance in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning