Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to sum the bottom N values in Excel and Google Sheets
Topic:SUM & aggregationExcel & Google Sheets
=SUMPRODUCT(SMALL(B2:B6,{1,2,3}))✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Product | Score |
| A | 88 |
| B | 72 |
| C | 95 |
| D | 65 |
| E | 80 |
=SUMPRODUCT(SMALL(B2:B6,{1,2,3}))→65
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Identify the range containing your values, e.g. B2:B6.
- 2Decide how many bottom values to sum (N). For N=3 enter =SUMPRODUCT(SMALL(B2:B6,{1,2,3})). The array constant {1,2,3} tells SMALL to return the 1st, 2nd, and 3rd smallest values.
- 3Press Enter. Adjust the array constant to match your desired N (e.g. {1,2,3,4,5} for N=5).
Tips
- For a dynamic N stored in a cell (e.g. D1), use =SUMPRODUCT(SMALL(B2:B6,ROW(INDIRECT("1:"&D1)))) — ROW(INDIRECT()) generates the rank array automatically.
- In Excel 365 / Google Sheets you can also use =SUM(SMALL(B2:B6,SEQUENCE(3))) where SEQUENCE(3) produces {1;2;3}.
- SMALL ignores text but includes zeros; verify your data has no stray zeros that should be excluded.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
How do I sum the bottom N values excluding zeros?
Filter zeros first: =SUMPRODUCT(SMALL(IF(B2:B6>0,B2:B6,""),{1,2,3})) entered as a regular formula in Excel 365 / Sheets, or Ctrl+Shift+Enter in older Excel.
What is the difference between SMALL and MIN?
MIN returns only the single smallest value. SMALL(range, k) returns the k-th smallest, allowing you to retrieve multiple ranked values.
More on SUM & aggregation
See all →Reference
SUMPRODUCT
Multiply ranges together row by row and sum the result — ideal for weighted totals.
How-toHow to sum a column
Add up an entire column with SUM. Total a whole column or a fixed range — identical in Excel and Google Sheets.
Error fixFix 0
SUM returns 0 or the wrong total in Excel and Google Sheets — fix numbers stored as text, filtered ranges, manual calculation mode, and circular references.
ReferenceSUBTOTAL
Aggregate a range (sum, average, count…) while ignoring other SUBTOTALs and, optionally, hidden rows. Ideal under filters.
How-toHow to create a running total
Build a cumulative running total with a SUM and a clever anchored reference that grows as you copy it down. Works in both apps.
How-toHow to sum values by month
Total amounts that fall in a given month with SUMIFS and two date bounds. Works the same in Excel and Google Sheets.