You may often need to sum the numbers between two values. For example, the sum of the numbers between 300 and 800.
Example: You are working with a worksheet with the product in column A, the amount in column B, and the profit/loss in column C.
Question: What is the sum of the numbers between 300 and 800 in column B?
You can use the SUMIFS function to count the number of cells between two values.
=SUMIFS(B2:B10, B2:B10, ">300", B2:B10, "<800")
The result returns 1,088.
Explanation:
- Step 1: B2:B10, ">300": Five values are greater than 300;
- Step 2: B2:B10, "<800": Six values are less than 800;
- Step 3: Two values are between 300 and 800: B4 (588) and B10 (500).
You can get the sum of the numbers in column C when the corresponding cells in column B are between 300 and 800.
=SUMIFS(C2:C10, B2:B10, ">300", B2:B10, "<800")
Notes: The SUMIFS Function
The SUMIFS function is to sum the values in a range that meet multiple criteria that you specify.
Formula:
=SUMIFS(sum_range, range, criteria, range, criteria)
Explanations:
- Sum_range is required; the range of cells that you want to sum.
- Range is required, the range you specified by criteria.
- Criteria is required; the criteria that defines which cells will be added.