SUMIFs Between Two Numbers in Excel (Easiest Way in 2024)
In this article, we will show you how to use SUMIFS between two numbers in Excel. Simply follow the steps below.
SUMIFS Between Two Numbers in Excel
The SUMIFS function in Excel is used to sum values in a range based on multiple criteria. If you want to sum values that fall between two numbers, you would use SUMIFS by specifying the range to sum and the criteria range with the corresponding conditions.
Syntax
Here’s the basic syntax for using Excel SUMIFS between two numbers:
=SUMIFS(sum_range, criteria_range1, “>”&lower_bound, criteria_range2, “<“&upper_bound)
Where:
sum_range: This is the range of cells that you want to sum.
criteria_range1: This is the first range of cells where you set the lower bound criteria.
“>”&lower_bound: This is the condition that the values in criteria_range1 must exceed (> is the “greater than” operator).
criteria_range2: This is the second range of cells where you set the upper bound criteria.
“<“&upper_bound: This is the condition that the values in criteria_range2 must be less than (< is the “less than” operator).
How to Use SUMIF Between Two Numbers in Excel
Follow the process below to use SUMIF between two numbers in Excel.
1. Set Lower and Upper Limits
Before applying the formula, decide on the lower and upper bounds that you want to use for summing the values. For example, let’s set the lower limit to 150 (in cell F2) and the upper limit to 400 (in cell F3).
2. Construct the SUMIFS Formula
In a cell where you want the result, input the SUMIFS formula. Remember the syntax:
=SUMIFS(sum_range, criteria_range1, “>”&lower_bound, criteria_range2, “<“&upper_bound)
In our case, to sum sales figures with values between 150 and 400, the formula would be:
=SUMIFS(C2:C10, C2:C10, “>”&F2, C2:C10, “<“&F3)
3. Press Enter and Verify the Result
Press Enter to calculate the SUMIFS formula. This action computes the sum of sales figures that meet the specified criteria.
How to Use Excel SUMIF Between Two Numbers with Criteria
Follow the steps below to use the SUMIF between two numbers with additional criteria in Excel.
1. Define Your Criteria for Summing
Determine the conditions for summing values between two numbers in your dataset. Specify the lower limit as 150 and the upper limit as 400, and choose the desired category as “A.” This step sets the boundaries and filters for the summing operation.
2. Construct the SUMIFs Formula Between Two Numbers With Criteria
In an empty cell, construct the following SUMIFS formula tailored to your dataset. The syntax:
=SUMIFS(sum_range, criteria_range1, “>”&lower_limit, criteria_range2, “<“&upper_limit, [optional_criteria_range3], [optional_criteria3], …)
Where:
sum_range is the range of cells that you want to sum.
criteria_range1 is the first range of cells to which the first criterion (lower limit) is applied.
“>”&lower_limit specifies that the value should be greater than the lower limit.
criteria_range2 is the second range of cells to which the second criterion (upper limit) is applied.
“<“&upper_limit specifies that the value should be less than the upper limit.
[optional_criteria_range3], [optional_criteria3], … are additional ranges and criteria that you can add if needed.
In our case, the formula would be:
=SUMIFS(C2:C10, C2:C10, “>”&F3, C2:C10, “<“&F4, B2:B10, F2)
This formula calculates the sum of sales values (in range C2:C10) where the corresponding values (in range A2:A10) are greater than 150, less than 400, and the category in column B (range B2:B10) is “A.” It precisely targets the data meeting your specified criteria.
3. Execute the Formula and View the Result
After entering the formula, press Enter to execute it. Excel will process the data according to your criteria and display the sum in the designated cell. This step confirms the accuracy of your summing operation.
We hope that you now have a better understanding of how to use SUMIF between two numbers in Excel. If you enjoyed this article, you might also like our article on how to use Excel SUMIF between dates or our article on using Excel SUMIFS greater than date.
