How to Sum Cells Between Two Values

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.

Leave a Reply