Sum if between

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.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar