SUMIF between two numbers in Excel and Google Sheets

SUMIFS between two values in Excel

The formula works in Excel 2007 - Excel 365

To sum data between two numbers, you can use the SUMIFS function with two criteria.

Including the threshold numbers:

Excluding the threshold numbers:

The difference between the two formulas is only in the logical operators:

• To include the threshold values in the sum, the greater than or equal to (>=) and less than or equal to (<=) operators are used.
• To exclude the threshold numbers, use greater than (>) and less than (<).

For example, to sum the numbers in the range C2:C10 that are greater than 200 and less than 300, the formula is:

=SUMIFS(C2:C10, C2:C10, ">200", C2:C10, "<300")

To make the formula more flexible, you can input the smallest and largest numbers in predefined cells (F2 and F3 in this example) and reference those cells. In this case, the syntax of the criteria is slightly different: you enclose a logical operator in double quotes and concatenate a cell reference using an ampersand. For example:

=SUMIFS(C2:C10, C2:C10, ">"&F2, C2:C10, "<"&F3)

SUMIF between two values in Excel

The formula works in Excel 2000 - Excel 365

If you have an old version of Excel where the SUMIFS function is not available, then you can emulate the functionality by using its singular counterpart - the SUMIF function.

The idea is to construct two separate SUMIF functions:

• The first one adds up the values greater than the minimal number.
• The other one sums the values greater than or equal to the maximum number.

Then, you subtract the latter from the former and get the desired result.

To make sure the formula works as expected, let's test it on the same dataset:

=SUMIF(C2:C10, ">200") - SUMIF(C2:C10, ">=300")

Or with the cell references:

=SUMIF(C2:C10, ">"&F2) - SUMIF(C2:C10, ">="&F3)

SUMIFS between two numbers with additional criteria

The beauty of the SUMIFS function is that accepts multiple range/criteria pairs (up to 127). Meaning, in the generic SUMIFS formula discussed above, you can include as many different criteria as needed.

For example, to sum the Grapes sales between $200 and $300, the formula takes this form:

=SUMIFS(C2:C10, C2:C10, ">200", C2:C10, "<300", B2:B10, "Grapes")

With cell references, it works equally well:

=SUMIFS(C2:C10, C2:C10, ">"&F3, C2:C10, "<"&F4, B2:B10, F2)

SUMIF between in Google Sheets

The good news is that the SUMIFS formulas we've built for Excel work in Google Sheets too, without a single change in the syntax. Thank you for the consistency, Microsoft and Google! :)

SUMIFS between two numbers

To get a sum of the values in column C that fall in the range between the numbers in F2 and F3, the formula is:

=SUMIFS(C2:C10, C2:C10, ">"&F2, C2:C10, "<"&F3)

SUMIFS between two values with another condition

To add up the sales numbers for a specific item in F2 that are between the numbers in F3 and F4, the formula is:

=SUMIFS(C2:C10, C2:C10, ">"&F3, C2:C10, "<"&F4, B2:B10, F2)

Practice workbooks

SUMIF between values in Excel (.xlsx file)
SUMIF between numbers in Google Sheets (online sheet)