SUMIF between two numbers in Excel and Google Sheets

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:

SUMIFS(values, values,">=min_number", values, "<=max_number")

Excluding the threshold numbers:

SUMIFS(values, values, "> min_number", values, "<max_number")

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) SUMIFS between two values in Excel

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.

SUMIF(values, ">min_number") - SUMIF(values, ">=max_number")

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) SUMIF between two values in Excel

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) SUMIFS formula between two numbers with other criteria

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 numbers in Google Sheets

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) SUMIFS between two values with another condition in Google Sheets

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)

Table of contents

6 comments

  1. Hi Team,
    In Excel workbook a sheet called A, there is a cell called a1, there is value called 1+2. Now, in the same work book another sheet called B, I want only the value of the first number of the cell a1 if sheet A.

  2. i need an excel formula for Average the sales that were made in Amazon and fall under the Toy Category

  3. 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?

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)