SUMIF Between Two Numbers in Excel (Step-by-Step)

Formulas & functions · Updated November 22, 2026

To sum values that fall between two numbers in Excel, use the SUMIFS function with two criteria: one for the lower bound and one for the upper bound. For example, =SUMIFS(C2:C10, C2:C10, ">=10", C2:C10, "<=20") sums all numbers in C2:C10 that are 10 or greater and 20 or less. This article explains the formula in detail, how to handle inclusive/exclusive bounds, and common pitfalls.

Why SUMIFS Instead of SUMIF?

The older SUMIF function only accepts a single condition. To sum between two numbers, you need two conditions: one for the lower limit and one for the upper limit. SUMIFS is designed for multiple criteria and is the standard tool for this task. It works in Excel 2010 and later, including Excel for Microsoft 365, Excel 2021, and Excel for the web. If you are using an older version, SUMIFS is still available in Excel 2007 and later.

Basic Syntax and Example

Assume your numbers are in cells C2:C10. To sum values that are greater than or equal to 10 and less than or equal to 20, use:

=SUMIFS(C2:C10, C2:C10, ">=10", C2:C10, "<=20")

This formula adds up every cell in C2:C10 that meets both criteria. If C2:C10 contains 5, 12, 18, 25, 10, 20, 15, 8, and 30, the result is 75 (12+18+10+20+15). Notice that 10 and 20 are included because the criteria use ">=" and "<=".

Inclusive vs. Exclusive Bounds

You control whether the boundary numbers are included by choosing the comparison operators:

  • Inclusive both ends: ">=10" and "<=20" includes 10 and 20.
  • Exclusive both ends: ">10" and "<20" excludes 10 and 20.
  • Lower inclusive, upper exclusive: ">=10" and "<20" includes 10 but not 20.
  • Lower exclusive, upper inclusive: ">10" and "<=20" excludes 10 but includes 20.

Be precise with the quotation marks and the comparison operators. A common mistake is to write "10<=C2<=20" which Excel does not understand. Always use separate criteria pairs.

Using Cell References for Bounds

Instead of hardcoding the numbers, you can reference cells that contain the lower and upper limits. For example, with the lower bound in E1 and the upper bound in F1:

=SUMIFS(C2:C10, C2:C10, ">="&E1, C2:C10, "<="&F1)

The ampersand (&) concatenates the comparison operator with the cell value. This makes the formula dynamic: change E1 or F1 and the sum updates automatically. Ensure the bound cells contain numeric values, not text.

Summing Based on a Different Column

Often you want to sum values in one column based on criteria in another. For instance, sum sales (column B) for dates (column A) between two dates. With dates in A2:A10 and sales in B2:B10, and start date in D1 and end date in E1:

=SUMIFS(B2:B10, A2:A10, ">="&D1, A2:A10, "<="&E1)

This sums all sales where the date is on or after D1 and on or before E1. The same pattern works for text criteria, but for numeric ranges this is the standard approach.

Common Errors and How to Fix Them

Several issues can cause errors or wrong results:

  • #VALUE! error: Often caused by text in the sum range or criteria range. Ensure all values are numeric.
  • Wrong results due to text numbers: If numbers are stored as text, SUMIFS may ignore them. Convert text to numbers using the VALUE function or Text to Columns.
  • Using SUMIF with two criteria: SUMIF cannot handle two conditions. Always use SUMIFS.
  • Quotation marks missing: Criteria like >=10 must be in quotes: ">=10". If you use a cell reference, concatenate with &.
  • Boundary inclusion confusion: Double-check whether you want to include the endpoints. Test with a small dataset.

Alternative: SUMPRODUCT

If you prefer a formula that works in older Excel versions or want more flexibility, SUMPRODUCT can also sum between two numbers. For example, to sum C2:C10 between 10 and 20 inclusive:

=SUMPRODUCT((C2:C10>=10)*(C2:C10<=20)*C2:C10)

This multiplies two logical tests (which become 1 or 0) by the values and sums the result. It works in all Excel versions and does not require array entry. However, SUMIFS is generally faster and easier to read, especially with large datasets.

Frequently Asked Questions

Can I use SUMIF to sum between two numbers?

No, SUMIF only supports one condition. To sum between two numbers, you need SUMIFS or SUMPRODUCT. SUMIFS is the recommended function because it is purpose-built for multiple criteria.

How do I sum values greater than a number but less than another?

Use SUMIFS with two criteria: =SUMIFS(C2:C10, C2:C10, ">10", C2:C10, "<20") for exclusive bounds. Adjust the operators to include or exclude the boundaries as needed.

Why is my SUMIFS returning 0?

Check that the criteria ranges are the same size as the sum range. Also verify that the criteria are written correctly with quotes and that the values are numeric. If the numbers are stored as text, SUMIFS may ignore them.

Can I sum between two dates with SUMIFS?

Yes, use the same pattern with date values. For example, =SUMIFS(B2:B10, A2:A10, ">="&D1, A2:A10, "<="&E1) sums B2:B10 where A2:A10 falls between the dates in D1 and E1. Ensure the dates are real Excel dates, not text.

Does SUMIFS work in Google Sheets?

Yes, SUMIFS works in Google Sheets with the same syntax. The main difference is that Google Sheets uses comma separators (which Excel also uses) and the formula is entered in the same way.

What if I want to sum values that are exactly between two numbers, like 10 and 20, but not including 10 and 20?

Use strict inequality: ">10" and "<20". This excludes both boundaries. If you want to include one but not the other, use ">=" or "<=" accordingly.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.