1

I'm importing a range from another spreadsheet using =IMPORTRANGE() which results in the following:

enter image description here

I need to sum() for groups of three month (every quarter), i.e., sum Jan, Feb, Mar in column 1, Apr, May, Jun in column 2, and so on...

How do I accomplish this in Google Sheets?

2 Answers 2

2

Assuming your data starts at col A and row 5 and extends to row 7 paste the below formula in column A and drag and autofill the formula across in the same row.

=sum(INDEX(transpose($A$5:$L$7),(column()-1)*3+1))+sum(INDEX(transpose($A$5:$L$7),(column()-1)*3+2))+sum(INDEX(transpose($A$5:$L$7),(column()-1)*3+3))

If your starting column is different from 1(Col A) then change the offset to the column number you are starting the formula at. So for column D you modify the below from

column()-1

to this

column()-4

The ways this works is to transpose the data array and select one column at a time using index, to be summed. The (column()-1)*3+1,2,3 generates index for next three columns to be summed.

0
1
=sum(offset($N5,0,(column()-19)*3,1,3))

where $N5 is the date for Jan. and "19" is the column() value for the starting column of the new, quarterly table.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.