Google-sheets – Returning the SUM of a range of cells from user-input START to END

google sheetsworksheet-function

In Google Docs, I am trying to sum the values of selected rows in a table, from position START to END.

Imagining this sample data:

Index  VALUE
  1     300
  2     400
  3     750
  4     850
  5     900
  6     1000

And two cells where I input START and END:

3
5

I would like the result to be the sum of columns from value: 750 + 850 + 900

I am trying to use SUMIF(range,criteria,sum_range) but cannot seem to specify a way to use 'between(min, max)' as criteria…

Best Answer

Ok - this isn't simple but it can be done. There may be a more elegant way but here's a way that will work, using only the terms you are providing.

Take a range of cells as follows

Col  A   B       C
2    1   300     2
3    2   450     4
4    3   700    
5    4   900    
6    5   1000   
7    6   1200

Take these formulas:

Cell A10: 
=sumif(A2:A7,"<="&C2,B2:B7)

Cell A11:
=sumif(A2:A7,">="&C3,B2:B7)

Cell B10:
=sum(B2:B7)

Cell A12:
=(A10+A11-B10)*-1

Cell A12 will have the results you want. Hopefully you get the idea.

Basically you sum all the data elements you don't want in your range, subtract that from the total sum of the range and the remainder is the sum of the range you want.

I didn't do much bug checking for corner cases, but hopefully this will give you enough of a start to solve the problem?

Related Topic