Sum if Between in Excel: Formula & Examples

This calculator sums values that fall between two numbers you specify. Enter the range of values to check, the lower bound, and the upper bound, and it returns the total of all matching entries. The result is the sum of every value that is greater than or equal to the lower bound and less than or equal to the upper bound.

Formula

For a list of values v₁, v_{2, …, vn, the sum between lower bound L and upper bound U is:}

Sum = Σ v_i for all i where L ≤ v_i ≤ U

• v_i – each value in the list (same unit as the bounds)
• L – lower bound (inclusive)
• U – upper bound (inclusive)

In spreadsheet terms, this is often written as SUMIFS(values, values, ">="&L, values, "<="&U) or SUMPRODUCT((values>=L)*(values<=U)*values).

Worked Example

Using the defaults: values = 10, 25, 30, 45, 60; lower bound = 20; upper bound = 50.

1. Check each value against the bounds: 10 (no), 25 (yes), 30 (yes), 45 (yes), 60 (no).
2. Add the qualifying values: 25 + 30 + 45 = 100.
3. The calculator returns 100.00.

When to Use This Calculation

“Sum if between� is a common data-filtering task in finance, inventory, and analytics. You might use it to:

• Total sales transactions between $100 and $500.
• Sum hours worked by employees who clocked between 30 and 40 hours.
• Add up test scores that fall in a passing range (e.g., 60–100).
• Calculate the combined weight of packages within a shipping tier.

The same logic applies whether the values are currency, counts, measurements, or any numeric quantity. The bounds can be decimals or negatives; the formula treats them as plain numbers.

How to Choose the Bounds

Decide whether the bounds are inclusive or exclusive. Most spreadsheets use inclusive (≥ and ≤) by default with SUMIFS. If you need exclusive (strictly greater than and less than), adjust the comparison operators:

• Inclusive: SUMIFS(values, values, ">="&L, values, "<="&U)
• Exclusive: SUMIFS(values, values, ">"&L, values, "<"&U)

For example, with values 10, 20, 30 and bounds 10 and 30, inclusive gives 60, exclusive gives 20. Always confirm which convention your data requires.

Common Mistakes and Misconceptions

One frequent error is mixing up the lower and upper bounds – the formula will return 0 or an error if L > U. Another is forgetting that text-formatted numbers won’t be summed; the values must be numeric. Also, blank cells are ignored, but cells with zero are included, which can skew results if you intended to skip zeros.

A common misconception is that “between� always means exclusive of the endpoints. In most spreadsheet functions, it means inclusive. If your source data uses “between� in a different way, adjust the operators accordingly.

Limitations and Alternatives

This simple sum assumes each value is independent and equally weighted. It does not handle:

• Multiple criteria on different columns (use SUMIFS with multiple ranges).
• Criteria based on dates or text (use SUMIFS with date/text conditions).
• Weighted sums or averages (use SUMPRODUCT or AVERAGEIFS).

For a quick check, you can also use a pivot table or filter the data and read the status bar sum in Excel. For large datasets, a database query (SQL) with WHERE value BETWEEN L AND U is more efficient.

Frequently Asked Questions

Does “between� include the endpoints?

In most spreadsheet formulas and in this calculator, yes – it includes both the lower and upper bound. If you need exclusive bounds, change the comparison to strict greater-than and less-than.

What if my values are in separate columns?

This calculator expects a single list. In Excel, you can combine columns into one list or use SUMIFS with multiple ranges – for example, SUMIFS(A:A, B:B, ">="&L, B:B, "<="&U) sums column A where column B meets the criteria.

Can I use negative numbers as bounds?

Yes. Negative numbers work exactly like positive ones. For instance, summing values between -10 and -5 will include -6 but not -4.

Why does my sum return 0?

Check that the values are stored as numbers, not text. Also verify that the lower bound is less than or equal to the upper bound, and that at least one value actually falls in the range. A common issue is having the bounds reversed.

How do I sum if between two dates?

This calculator only handles plain numbers. In Excel, use SUMIFS with date criteria, e.g., SUMIFS(amounts, dates, ">="&DATE(2024,1,1), dates, "<="&DATE(2024,12,31)). Dates must be stored as real dates, not text.

Related

• Samsung SRF7300SA: What to Know Before Buying
• iPhone X Battery Percentage: How to Show It
• Garden Horizons Codes & How to Redeem Them

in values | Values (comma separated) | - | 10, 25, 30, 45, 60 | 0
in low | Lower bound | - | 20 | 0
in high | Upper bound | - | 50 | 0
out total | Sum between bounds | - | sum(filter(values, v >= low and v