by Alexander Frolov, updated on
The tutorial shows how to sum data for a range between two values by using the SUMIF and SUMIFS functions.
For large datasets, you may often need to calculate a total amount for a given range, i.e. sum data based on two conditions: "greater than A" and "less than B". This can be easily done with the help of the SUMIFS function both in Excel and Google Sheets.
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:
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)
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:
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)
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)
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)
That's how to sum data between two values in Excel and Google Sheets. Amazingly simple, isn't it? I thank you for reading and hope to see you on our blog next week!
SUMIF between values in Excel (.xlsx file)
SUMIF between numbers in Google Sheets (online sheet)
Table of contents