Sum Bottom N Values with Criteria in Excel

This article explains how to sum the smallest n values in a range that meet one or more conditions, a common task in Excel and Google Sheets. You’ll learn the exact formulas using SMALL, IF, and SUMPRODUCT, with worked examples and tips for avoiding errors.

Formula

To sum the smallest n values in a range that meet a condition, use an array formula that combines IF and SMALL, then wraps them in SUMPRODUCT to sum the resulting array. In Excel 365 and Google Sheets, you can use the dynamic array version directly. The general formula is:

=SUMPRODUCT(SMALL(IF(criteria_range=criteria_value, data_range), ROW(INDIRECT("1:"&n))))

Where:

• data_range – the range containing the numeric values to sum.
• criteria_range – the range containing the condition to test (same size as data_range).
• criteria_value – the value that must be matched in criteria_range.
• n – the number of smallest values to sum.
• ROW(INDIRECT(“1:”&n)) – generates an array of numbers from 1 to n, which SMALL uses to return the 1st, 2nd, …, nth smallest values.

In Excel 365 or Google Sheets, you can use the SEQUENCE function instead of ROW(INDIRECT(…)): =SUMPRODUCT(SMALL(IF(criteria_range=criteria_value, data_range), SEQUENCE(n))). In older Excel versions, you must enter the formula as an array formula by pressing Ctrl+Shift+Enter.

Worked Example

Suppose you have the following data: values in A1:A10 are 10, 20, 30, 40, 50, 60, 70, 80, 90, 100, and criteria in B1:B10 are 1,1,0,1,0,1,0,1,0,1. You want to sum the 3 smallest values where the criteria equals 1.

1. Identify values where B=1: 10, 20, 40, 60, 80, 100.
2. Sort these ascending: 10, 20, 40, 60, 80, 100.
3. Take the 3 smallest: 10, 20, 40.
4. Sum them: 10 + 20 + 40 = 70.

The formula =SUMPRODUCT(SMALL(IF(B1:B10=1, A1:A10), ROW(INDIRECT("1:3")))) returns 70, matching the manual calculation.

How to Use the Formula in Excel and Google Sheets

Enter the formula as an array formula in Excel 2019 and earlier (Ctrl+Shift+Enter). In Excel 365 and Google Sheets, it works as a normal formula. Steps:

• Select the cell where you want the result.
• Type the formula, adjusting ranges and n.
• If using Excel 2019 or earlier, press Ctrl+Shift+Enter to confirm. If done correctly, Excel wraps the formula in curly braces {}.
• In Google Sheets, simply press Enter.

For a non-array alternative, use SUMPRODUCT with SMALL and IF, but remember that SUMPRODUCT can handle arrays natively, so you don’t need Ctrl+Shift+Enter in most cases. However, the SMALL function itself requires an array as its second argument, so the array entry is still needed in older Excel.

Common Mistakes and How to Avoid Them

• Forgetting array entry – In Excel 2019 and earlier, not pressing Ctrl+Shift+Enter gives a #VALUE! error or wrong result.
• Mismatched range sizes – data_range and criteria_range must be exactly the same size, or the formula returns #VALUE!.
• Using n larger than the number of matching values – SMALL returns #NUM! if you ask for the k-th smallest when there are fewer than k values. Use IFERROR to handle this gracefully.
• Criteria with text or blanks – If criteria_value is text, enclose it in quotes (e.g., “Yes”). If criteria_range contains blanks, they are treated as 0, not as empty.
• Using SUM instead of SUMPRODUCT – SUM with an array requires Ctrl+Shift+Enter, but SUMPRODUCT is more robust and doesn’t need array entry in most cases.

Alternatives and Variations

If you need to sum the bottom n values without any criteria, use =SUM(SMALL(range, ROW(INDIRECT("1:"&n)))) (array entered). For summing the top n values, replace SMALL with LARGE. If you have multiple criteria, you can multiply conditions inside the IF function: =SUMPRODUCT(SMALL(IF((criteria1=val1)*(criteria2=val2), data_range), ROW(INDIRECT("1:"&n)))). For Excel 365, you can use the FILTER function to extract matching values first, then take the smallest n with SMALL and sum: =SUM(SMALL(FILTER(data_range, criteria_range=val), SEQUENCE(n))). This is often clearer and easier to debug.

When to Use This Formula

Use this formula when you need to calculate a sum of the lowest values that meet a condition, such as the lowest 3 sales per region, the cheapest 5 products in a category, or the minimum 2 test scores per student. It is useful in financial analysis, inventory management, and academic grading. The formula is dynamic – if the data changes, the result updates automatically. However, it is an array formula, so it can be slower on large datasets (over 10,000 rows). For very large data, consider using a pivot table or Power Query.

Limitations and Performance Tips

Array formulas can be computationally heavy. If you have thousands of rows, the formula may slow down your workbook. To improve performance, limit the ranges to the actual data size, avoid full-column references like A:A. Also, if you are using Excel 365, the dynamic array functions (FILTER, SEQUENCE) are generally more efficient. Another limitation: the formula only works with numeric values; if your data contains text or errors, it may return #VALUE!. Use IFERROR to handle errors gracefully. Finally, remember that SMALL ignores text and logical values in the data range, but if the criteria range contains text, you must match it exactly.

Frequently Asked Questions

Why does my formula return #VALUE!? This usually happens when the data_range and criteria_range are not the same size, or when you didn’t enter the formula as an array formula in older Excel versions. Check that both ranges have the same number of rows and columns, and press Ctrl+Shift+Enter if needed.

How do I sum the bottom n values with multiple criteria? Multiply the conditions inside the IF function: =SUMPRODUCT(SMALL(IF((range1=val1)*(range2=val2), data_range), ROW(INDIRECT("1:"&n)))). Enter as an array formula if using older Excel.

Can I use this formula in Google Sheets? Yes, it works in Google Sheets as a normal formula, since Google Sheets handles array formulas automatically. You can also use the FILTER and SEQUENCE functions for a cleaner approach.

What if there are fewer than n values that meet the criteria? SMALL will return #NUM! because it cannot find the k-th smallest value. Wrap the formula in IFERROR to return a custom message, like =IFERROR(your_formula, "Not enough values").

How do I sum the bottom n values ignoring zeros? Add a condition to exclude zeros in the criteria: =SUMPRODUCT(SMALL(IF((data_range<>0)*(criteria_range=val), data_range), ROW(INDIRECT("1:"&n)))).

Is there a way to do this without array formulas? In Excel 365, you can use FILTER and SEQUENCE, which are dynamic and don’t require Ctrl+Shift+Enter. In older versions, you could use a helper column to filter values, then use SMALL and SUM on that column, but that requires manual steps.

Related

• Trail Camera Data Recovery: How to Get Photos Back
• Kirby Air Riders Narrator & Announcer Voice
• Chrome Android Label Fast Pages?

in n | n (number of values) | - | 3 | 1
in range | Data range (e.g. A1:A10) | - | 10,20,30,40,50,60,70,80,90,100 | 0
in crit | Criteria range (e.g. B1:B10) | - | 1,1,0,1,0,1,0,1,0,1 | 0
in val | Criteria value | - | 1 | 0
out sum | Sum of bottom n with criteria | - | sum(small(if(crit=val,range),{1,2,3})) | 2