Function Problems

How to Sum the Three Lowest Numbers in an Excel Range

Tauseeq Magsi Sep 25, 2026 869 views

Question details

The user wants to calculate the sum of the three smallest numerical values within a specific range of five cells in Excel.

How to Sum the Three Lowest Numbers in an Excel Range

Product: Excel
Device & OS: not provided
Scenario: Calculating the sum of the lowest values in a dataset for mathematical evaluation or data analysis.
Observed behavior: The user needs a reliable formula to identify and add only the three lowest numbers from a given cell range without manually sorting the data.

Before you start

Ensure your target dataset contains at least three numerical values and does not contain error values, as errors will prevent the formula from calculating properly.

Solution 1Recommended

Use the SUM, SMALL, and SEQUENCE Functions

This is the most reliable method to sum a specific number of the smallest values in a range, effectively combining sorting and summing logic into one step.

The SMALL function is designed to return the k-th smallest value in a data set. By combining it with SEQUENCE (or an array constant), you can extract multiple lowest values at once, which the SUM function then totals.

Select the target cell

Click on the empty cell where you want the final sum to appear.

Enter the primary formula

Type the formula =SUM(SMALL(A1:A5,SEQUENCE(3))) into the formula bar. Replace 'A1:A5' with your actual data range.

Evaluate the result

Press the Enter key. The formula extracts the 1st, 2nd, and 3rd smallest numbers from the range and immediately adds them together.

Use the SUM, SMALL, and SEQUENCE Functions

Alternative Array Method: If your version of Excel does not support the SEQUENCE function, you can use a hardcoded array constant instead: =SUM(SMALL(A1:A5,{1,2,3})).

Solution 2

Use the TAKE and SORT Functions

For users on the latest versions of Excel (such as Microsoft 365), this dynamic array formula offers a modern approach to extracting and summing the smallest values.

Efficient Data Calculation with WPS Office

Easily Sum Lowest Values in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas, including SUM and SMALL, allowing you to seamlessly calculate lowest or highest values in your datasets just like in Microsoft Excel.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the file containing your numerical data.
2. Verify your data range: Ensure your numbers are listed in a clear range, such as cells A1 through A5.
3. Apply the array formula: Type =SUM(SMALL(A1:A5,{1,2,3})) into a blank cell and press Enter to instantly see the sum of the three lowest values.

Fully compatible with Microsoft Excel (.xlsx) file formats and complex array formulas.Natively supports advanced statistical functions like SUM, SMALL, LARGE, and SORT.Free, lightweight, and features a familiar user interface with zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

How can I sum the three largest numbers instead of the lowest?

You can replace the SMALL function with the LARGE function. To sum the top three values, use the formula =SUM(LARGE(A1:A5,{1,2,3})) or =SUM(LARGE(A1:A5,SEQUENCE(3))).

Why does my formula return a #NUM! error?

A #NUM! error typically occurs if there are fewer numerical values in your selected range than the sequence you are trying to extract. For example, trying to extract the 3 smallest numbers from a range that only contains 2 numbers will trigger this error.

What happens if my cell range contains blank cells or text strings?

The SMALL function automatically ignores blank cells, logical values, and text strings. It will only evaluate and rank the actual numerical values within your specified cell range.

Can I modify the formula to sum the lowest 5 or 10 numbers?

Yes, you simply need to change the array or sequence number. For example, to sum the five smallest numbers in a larger dataset, use =SUM(SMALL(A1:A50,SEQUENCE(5))) or =SUM(SMALL(A1:A50,{1,2,3,4,5})).

Excel & Data Problems