I am currently using Google Sheets. Suppose: Cell C10 is my "Reference Cell". Cell C10 has a value of 9. Cell A10 is my "Total Cell". For Cell A10, I want to SUM the above x cells, based on the value in my "Reference Cell". In this example, Cell A10 would SUM the above 9 cells (since Cell C10 = 9) How can I do this?

Reputation: 35

SUM above x Cells based on value in Reference Cell

I am currently using Google Sheets. Suppose:

For Cell A10, I want to SUM the above x cells, based on the value in my "Reference Cell".

How can I do this?

Upvotes: 1

Reputation: 1

or from top:

=INDEX(QUERY(QUERY(C1:C9, "limit "&C10), "select sum(Col1)"), 2)

and bottom:

=INDEX(QUERY(QUERY(C1:C9, "offset "&9-C10), "select sum(Col1)"), 2)

from top:

=SUM(QUERY(C1:C9, "limit "&C10))

from bottom:

=SUM(QUERY(C1:C9, "offset "&9-C10))

Upvotes: 0

Reputation: 1908

If from bottom, in A10:

=sum(A9:offset(A10,-C10 ,0))

if from top, in A10:

=sum(A1:offset(A1,C10-1 ,0))

Upvotes: 1

Reputation: 1

try like this if from top:

=SUM(INDIRECT("C1:C"&C10))

if from the bottom do:

=SUM(INDIRECT("C"&ROW()-1-C10&":C"&ROW()-1))

Upvotes: 0