Excel sum if between function is a formula formed by combining sum and if between conditions. It implies that this function can add cells or inputs that satisfy a criteria 1 and criteria 2, between specific cell ranges on the excel sheet. The sum if between value remains valid as long as the criteria remain unchanged by a user.

**Generic Formula**

This formula appears on the column in which you want your final output to be generated

**=SUMIFS (sum_range,criteria_range,”>criteria1”,criteria_range,”<criteria 2”)**

**Explanation**

The excel worksheet has a built-in function called the SUMIFS function which adds all the numbers in a given cell (range) based on two criteria 1, 2. The SUMIFS function supports excels logical operators (i.e. “=”,”>”,”>=”, < etc. Depending on your criteria, you can choose any of these.

**Example**

Below are the inputs of a daily cash transaction at a local store. We are expected to calculate the sum of entries that are greater than $300 but less than $2000.

In the example below, criteria 1; >300, criteria 2; <2000. “Cost” is the range of entry for cells D5 to D11. Which must be inputted in the formula as D5:D11

**=SUMIFS(cost,cost,“>300”,cost,”<2000”) such that the formula **

`=SUMIFS(D5:D11,D5:D11,”>300”,D5:D11,”<2000”)`

NB: THERE SHOULD BE NO SPACE WHEN ENTERING THE FORMULA.

The function returns all values that are greater than $300 and less than $2000.

Figure 1. Example of SUMIFS between function on Excel

**Notes**

- Based on our example, your logical operators, in this case “<” and “>”, as well as threshold values should be supplied inside double quotes.
- If you want the function to return a sum of numbers that include amounts equal to the first and second criteria, use >= or <= in your formula.

## Leave a Comment