Friday, March 4, 2011


Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  

I am going to address here a case where not only the rows range is open-ended, but also the columns range is open-ended in that one can add more fields (columns) 

because the queried range can expand both horizontally and vertically, I thought it is best to take the SourceData in one Sheet and run the QUERY in tne ResultsSheet

What I am trying to compute here are Day totals (Day1, Day2,Day3, ....) for unique items in column A.

So, here we go ...

If more fields (columns), say Day4, Day5, etc. are added to the SourceDta, the Results Sheet will self adjust and expand to suit.


  1. Yogi,
    I am a retired Civil Engineer form the UK!

    Interested in your blog... and having problems creating a summary of results from a fixtures/ results list prior to creating a league table.

    I have a fixtures/ results table:-
    Home Away
    Owston Ferry 43 v 22 Scotter
    Scotter 40 v 29 Belton
    Scotter 42 v 28 Gainsborough
    Belton 32 v 33 Owston Ferry
    Gainsborough 2 28 v 43 Belton
    Owston Ferry 40 v 24 Scotter
    Scotter v Gainsborough
    Belton 46 v 35 Scotter 2

    What I am trying to devise is a summary for each team where the team name appears in a column and then there are columns for no. of matches, Played, Won, Drawn, Lost, for, Against, and points for both 'Home' and 'Away'.

    I can then add the two 'sets' together and sort to give the leading team.

    I thought that your formula would work... but I cannot get it to!

    Any ideas?


    Allan Thompson
    B.SC., C.Eng., MICE, MIHT

  2. Hi Allan:

    It is nice to hear from a fellow Civil Engineer ... I am also a Civil Engineer by profession. I am also retired but I do maintain a consulting practice, my areas of interest being Computers-Education-Engineering-Networking. In engineering I now do mostly structural engineering work in energy efficient buildings primarily with Insulating Concrete Forms. In computers my consulting work is in the area of spreadsheet based solutions. Well, enough of side chat.

    In regard to your Google spreadsheet issue, I suggest you share your spreadsheet for VIEW and EDIT along with your expected results. It will be better if post it in the Google Help Forum, where not only I but others can offer suggestions as well.