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.
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”)
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.
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
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
- 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.