0

I have a Google Spreadsheet that counts a number of events that happened per day. This is what it currently looks like:

I would like to have a 30-day total running alongside, i.e., the total events for every 30 days. It would look something like this:

Here, U7 consists of the sum of the first 30 events (T7:T36), U8 is the sum of the next 30 (T37:T66), U9 sums the next 30 and so on.

I can quite easily do this by filling all of the cells in column U with:

=SUM(OFFSET($T$7,30*(ROW()-7),0,30,1))

but this means every cell has to be filled and it is a bit slow in the scheme of this spreadsheet, particularly as I will need numerous columns like this. Is there a way to do this with a single filter in the top cell or something of the sort? I haven't been able to figure out a way to do that.

0

I think on U7 you can use =SUM(T7:T36), then drag the formula all the way down, or copy/paste it down. On U8 it should automatically change the formula to =SUM(T8:T37).

Another suggestion to improve the sheet would be to add another column for days, and keep S column as dates only (so remove the Sunday). Then you can do conditional formatting to highlight the entire row for all Sundays with the new column for days.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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