SUMIF Between Two Values in Excel: Formula & Examples

Formulas & functions · Updated November 18, 2026

To sum values between two limits in Excel, use SUMIFS, which applies both a lower and an upper condition to the same range. For example, if the values to test are in A2:A8, the amounts to add are in B2:B8, and the limits are in D2 and E2, use =SUMIFS(B2:B8,A2:A8,">="&D2,A2:A8,"<="&E2) for a total that includes both endpoints.

Why SUMIFS is the right function

The name of the task often leads people to search for “SUMIF,” but a range has two boundaries: the value must be at least the lower limit and no greater than the upper limit. Excel’s SUMIF accepts one condition. SUMIFS is designed to apply multiple conditions, so it is the straightforward choice for an inclusive range.

In the example formula, B2:B8 is the sum range: Excel adds numbers from those cells. A2:A8 is the criteria range, checked twice. The first condition keeps rows whose A value is greater than or equal to the lower limit in D2; the second keeps rows whose A value is less than or equal to the upper limit in E2. A row contributes to the total only when both tests are true.

For a concrete check, suppose A2:A8 contains 5, 10, 15, 20, 25, 30, and 35, while B2:B8 contains 50, 100, 150, 200, 250, 300, and 350. With D2 set to 10 and E2 set to 25, the result is 700: Excel adds 100 + 150 + 200 + 250. The values 10 and 25 are included because the criteria use “greater than or equal to” and “less than or equal to.”

How to enter the formula

On Microsoft 365 for Windows, enter the lower and upper limits in separate cells, then enter the formula in the cell where you want the result. Using the layout from the example above, the formula is:

With values in A2:A8, amounts in B2:B8, the lower limit in D2, and the upper limit in E2, enter =SUMIFS(B2:B8,A2:A8,">="&D2,A2:A8,"<="&E2). Excel returns the sum for rows whose A values fall within the limits.

In an HTML article, the ampersand in a displayed formula may appear as &amp; because HTML encodes that character. When you type the formula in Excel, use the ordinary ampersand character: =SUMIFS(B2:B8,A2:A8,">="&D2,A2:A8,"<="&E2). The ampersand joins the comparison operator to the value in the limit cell. Without it, Excel would not build a criterion such as “greater than or equal to 10” from the operator and cell reference.

  1. Check that the test values and amounts line up row by row.
  2. Put the lower limit in one cell and the upper limit in another.
  3. Enter the formula and press Enter. Change either limit to recalculate the total.

Excel for Mac uses the same function and formula structure. If your regional settings use semicolons as argument separators, Excel may require semicolons instead of commas; use the separator Excel accepts in your installation. This is a regional setting difference, not a different SUMIFS method.

Choosing inclusive or exclusive limits

Decide whether the endpoints count before choosing the comparison operators. The common “between” interpretation includes both endpoints. Use >= for the lower limit and <= for the upper limit. If the endpoints should not count, use strict comparisons instead.

With values in A2:A8, amounts in B2:B8, the lower limit in D2, and the upper limit in E2, this version sums only values strictly between the limits: =SUMIFS(B2:B8,A2:A8,">"&D2,A2:A8,"<"&E2). For limits of 10 and 25, it excludes rows where the tested value is exactly 10 or exactly 25.

Do not confuse “between” with “either limit.” SUMIFS combines its criteria with AND logic across criteria pairs: a row must satisfy the lower condition and the upper condition. That is exactly what a bounded interval needs. If you instead need to sum rows matching one range or another, such as values from 1 to 5 or from 20 to 25, you need to add separate totals or use another approach; those are two separate intervals.

Using SUMIF as an alternative

You can build a between-range total by subtracting one SUMIF result from another, but it is usually less clear than SUMIFS. This method adds everything up to the upper limit, then subtracts values below the lower limit. For an inclusive lower boundary, the subtracted portion must be strictly below that boundary.

With values in A2:A8, amounts in B2:B8, the lower limit in D2, and the upper limit in E2, an equivalent inclusive calculation is =SUMIF(A2:A8,"<="&E2,B2:B8)-SUMIF(A2:A8,"<"&D2,B2:B8). It returns the same 700 for the example data and limits of 10 and 25. This approach can be useful when a workbook already uses SUMIF, but it takes more effort to audit and is easier to get wrong at an endpoint. Prefer SUMIFS when both boundaries apply to one range.

Working with dates and times

Excel stores dates as serial values, so SUMIFS can compare dates using the same operators as it uses for numbers. Format the boundary cells as dates if you want them to display clearly, and ensure the date values in the criteria range are real Excel dates rather than text that merely looks like a date.

With transaction dates in A2:A100, amounts in B2:B100, a starting date in D2, and an ending date in E2, this formula includes transactions on both dates: =SUMIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<="&E2). It works as expected when the transaction cells contain dates without time portions.

A common complication is timestamps. If A2:A100 contains date-and-time values, the ending date in E2 represents midnight at the start of that day. A criterion of less than or equal to E2 can therefore exclude transactions later that same day. For timestamps, use the next day as an exclusive upper boundary.

With timestamps in A2:A100, amounts in B2:B100, the first date in D2, and the final calendar date in E2, use =SUMIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<"&E2+1). This includes every time on the final date while excluding midnight at the start of the following date. It assumes E2 contains a date without a time portion.

Common errors and how to fix them

  • The result is zero: Check that the bounds are in the right order, the criteria range contains the expected values, and the sum range contains numbers. A lower bound greater than the upper bound normally leaves no qualifying rows.
  • The formula reports a problem: Check the commas, quotation marks, parentheses, and ampersands. Comparison operators in criteria need to be inside straight quotation marks, and the operator must be joined to a cell reference.
  • The total is too high or low: Confirm that the formula uses the intended inclusive or exclusive operators. Also verify that the sum range and criteria range cover the same number of rows and begin on corresponding rows.
  • Numbers that look numeric are ignored: Some imported values may be stored as text. Convert them to numbers where appropriate, then check whether the criteria range and sum range contain the actual data you expect.
  • The formula changes when copied: Relative references such as A2:A8 shift when copied. If the ranges must stay fixed, use absolute references such as $A$2:$A$8. Keep limit references relative if you want them to move when copying the formula.

SUMIFS does not require the data to be sorted. Sorting can make a table easier to inspect, but it is not needed for the calculation. It is also worth checking for blank or error cells if results do not match a manual total; a quick filter on the criteria column can help reveal which rows meet the boundaries.

SUMIF, SUMIFS, and alternatives

Use SUMIF when there is just one condition, such as summing amounts for one department. Use SUMIFS when each row must pass two or more conditions, such as a value range plus a department, product, or region. The argument order differs: SUMIFS begins with the sum range, followed by criteria-range and criterion pairs; SUMIF puts its criteria range and criterion before the optional sum range.

For example, to limit the between-range calculation to one department, add a department criterion range and a department criterion to the SUMIFS formula. With values in A2:A100, amounts in B2:B100, departments in C2:C100, limits in D2 and E2, and the required department name in F2, use =SUMIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<="&E2,C2:C100,F2). The result includes only rows that meet both numeric bounds and match the department in F2.

A PivotTable can be a useful alternative when you want to explore totals by categories or repeatedly change how the data is grouped. For a single calculated total that updates as limits change, SUMIFS is typically simpler. If the data is arranged as an Excel Table, structured references can also make formulas easier to read and can expand automatically as rows are added, provided the formula refers to the appropriate table columns.

Frequently Asked Questions

Can SUMIF sum between two numbers?
SUMIF accepts one condition, so SUMIFS is the clearest function for a lower and upper bound. A subtraction of two SUMIF calculations is possible, but requires careful handling of whether endpoints are included.

Does between include the two endpoint values?
It depends on the comparison operators. Use greater than or equal to and less than or equal to to include both endpoints. Use greater than and less than to exclude them.

Can I use cell references for the limits?
Yes. Join each comparison operator to its limit cell with an ampersand, as in the examples. This lets you change the limits without editing the formula.

Can SUMIFS work with dates?
Yes. Excel dates are stored as numeric serial values, so date limits work with SUMIFS. For date-time data, use a less-than criterion for the day after the final date so that times later in the final day are included.

Why is my between-values formula returning zero?
Common causes include reversed limits, criteria values stored as text, mismatched range sizes, and criteria that are too restrictive. Check the source cells and boundary operators first, then compare the qualifying rows with the formula’s intended range.

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.