by 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.
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)
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!
Practice workbooks
SUMIF between values in Excel (.xlsx file)
SUMIF between numbers in Google Sheets (online sheet)
4 comments
i need an excel formula for Average the sales that were made in Amazon and fall under the Toy Category
Hi! To calculate average sales by multiple criteria, use the AVERAGEIFS function.
hi I have long data that was expported by SQL but i want to convert it
to wide data
may u help my how can i do this?
Hi Sajjad,
do you work in Excel or Google Sheets?