yogi_Compute Sum Up Numbers For Specified Number Of Months With Each Month Data Provided In Specified Number Of Columns

                                           Google Spreadsheet   Post  #1966
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jun 15, 2015

question  by mpacker:

https://productforums.google.com/forum/#!mydiscussions/docs/kB4Ca0yfyM4

formula to add every 3rd column to the left

i have a spreadsheet that has three columns for each month  we will call them x1,y1,z1  next month i insert three new columns after z1 and we will call them x2,y2,z2 and so forth.

there are a few columns at the very end and one of them i'm trying to get total for last 12 months of x.   i can do this with a formula i change each month but i'd really like some way to say take the column 3 to the left + 6 to the left + 9 to the left... till i get a total of 12 then when i insert to columns it automatically changes to the last 12...

any ideas?

hope that's understandable.

thanks!

---

Hi Yogi!

Here is a link to a sample of what i'm trying to do:

https://docs.google.com/spreadsheets/d/1MQbu83IL7Ss7NxrKmR8ANSUA5Syocm4OdP6QPRf9DU0/edit?usp=sharing

if you look at columns AL, AM and AN  those are always the last three columns.  Each month i add three new column (in this case AI, AJ and AK).

The formulas in AL, AM and AN are the ones i'm trying to figure out how to get a formula so that I don't have to edit each month.

The formula in AL3 is currently

=AH3+AE3+AB3+Y3+V3+S3

after AI-AK have been added what i really want AL3 to do is be the sum of V3+Y3+AB3+AE3+AH3+AK3  (basically dropping S3 and adding AK3)

same thing for AM and AN columns....  I'm wanting AL to AN to always be working off of the last 6 months.

does that make sense?  

I really appreciate the help.

---

I've modified the spreadsheet.

There are two tabs now.  

Month1 and "next month"

at the end of each month i add three columns.  the sheet would change from "month1" to what's in "next month".

when i do this i have to manually edit the formulas in ao3 to aq5

For example:

in month1:ao3 the formula is the sum of  v3+y3+ab3+ae3+ah3+ak3

when i add the columns next month i want a formula in ao3 to automatically change so i don't have to change ao3 to y3+ab3+ae3+ah3+ak3+an3 

the blue column should always be the sum of the 6 grey columns to the left of blue column.. when 3 new columns are added..

if played around with arrayformula and other things but i can't figure out how to write one that does this.

Thanks

--------------------------------------------------------------------------------------------------------------------------------