Wednesday, April 10, 2013

yogi_Compute Row By Row Miles Traveled From A Roster Of Textual Dates Names of Places And Miles

                                          Google Spreadsheet   Post  #1109
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI    Apr 11, 2013
user Pickle Pete :(!category-topic/docs/spreadsheets/ZLXVsfX562A)
ArrayFormula Sum of Specific Columns
I have a form posting to a spreadsheet like this:

   A           B         C         D          E         F             G         H           I            J ...
1 Name    Date    Desc     Miles    Date     Desc       Miles    Data      Desc      Miles...
2 Frank    04/01    FtoLA   180      04/02    LAtoSD   160       04/03     SDtoF    320
3 John     04/01    SDtoLA 160      04/02    LAtoF      190       04/03     FtoSD    330

The Date, Desc, and Miles repeat every 3rd column for as many times as it is inputted into the form. Now suppose I want to add all the miles in row 2, the formula would be 


Since this is a form, I would like an array formula to automatically calculate the next submitted entry. In my head it goes:

But obviously that does not work otherwise I wouldn't be here. Any other ideas?

Technically I am putting the formula in A:A and have an index formula parsing data from a javascript form in Cols B:BZ. The goal is to add up all the miles from each row into the A:A Column and have it auto repeat the next time a form entry is added.  
following is a solution to the problem