Forum Discussion

John Korduba's avatar
John Korduba
Copper Contributor
Jul 12, 2018

If formula

Is there a formula that would look at a range of cells and add up the values in those cells only if the value was within a certain range?  For instance, suppose I have the following range of cells:

cell   value

A1:  100

A2:  250

A3:  350

A4:  400

A5:  550

Let's say I want the formula to look at all the cells within range A1:A5, but I only want to add the cells whose values are within the number range greater than 200 but less than 400.  In this case, the result would be 250 + 350 or 600.

 

Thank you.

 

Formulas and Functions
  • Arul Tresoldi's avatar
    Arul Tresoldi
    Iron Contributor
    Jul 14, 2018

    You can use SUMIFS(A1:A5;A1:A5;">200";A1:A5;"<400")

     

    This formula sums all numbers in A1:A5 but only if in A1:A5 are >200 and in A1:A5 are <400. You have to repeat the A1:A5 in order to explain to the machine where to check the conditions, even if it's always the same range.

Resources