CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
German
German

Reputation: 126

how to sum last x rows from a column? Google sheets

I need to sum values in column H for the last 52 rows with a formula, starting in H3. Rows will be added regularly.

=query("H" & IF( COUNT(H3:H)-52+2<3,3, COUNT(H3:H)-52+2) & ":H" & COUNT(H3:H)+2,"select sum(H)",-1)

EDIT:

This is also not working, why? If I use concatenate I see "H5:H57"

=sum("H"& trim(IF( COUNT(H3:H)-52+2<3,3, COUNT(H3:H)-52+2)) & ":H" & trim(COUNT(H3:H)+2))

Upvotes: 0

Views: 866

Answers (1)

Marios
Marios

Reputation: 27400

You are creating a cell reference as a string, but you need to convert it to a cell reference.

You are looking for INDIRECT:

=sum(indirect("H"& trim(IF( COUNT(H3:H)-52+2<3,3, COUNT(H3:H)-52+2)) & ":H" & trim(COUNT(H3:H)+2)))

Upvotes: 1

Related Questions