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

Question

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

Answer 1

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?

Answer 2

Use worksheet function OFFSET. Suppose your values are in column B (starting from row 2) and that your start and end values are in range C2:C3. Then you have to input: =sum(offset(B1,C2;0,C3-C2+1,1)).

Answer 3

If you MIN is E1 and your MAX is F1 then this is the easy way:

=SUM(INDIRECT(ADDRESS(MATCH(E1,A1:A6),2) & ":" & ADDRESS(MATCH(F1,A1:A6),2)))

Answer 4

I can't give you a google-spreadsheets answer because I don't have access to it due to a firewall restriction, but I can give you an Excel based answer.

I don't know of a way to give a smart criteria, AFAIK it only allows for "dumb" conditions.

To do what you want you need to define assisting columns - and then hide them to avoid them being visible.

Below is an example of what it looks like:

A             B             C             D             E             F
300           1             No                          Min           Max
400           2             Yes                         2             5
750           3             Yes
850           4             Yes
900           5             Yes
1000          6             No            2900

Explanation:

• Column A -> contains your actual data
• Column B -> Is a helper column to get each value's Index. Its formula is: =MATCH(A1,$A$1:$A$6) (Replace A1 with the actual cell)
• Column C -> Helper column to say if the index is in the range. Its formula is: =IF(AND(B1>=$E$2,B1<=$F$2),"Yes","No") (Replace B1 with the actual cell)
• E2 contains the minimum index, F2 contains the maximum index.
• The D6 cell is the summation - it is defined as =SUMIF(C1:C6,"=YES",A1:A6)

I know it is very complicated, but that is the best I could come up with.

Below is how it looks before hiding the columns: