yogi_Compute Values In Columns Per Specified Criteria Then Sum Up Specified Columns By Row

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #522    Apr 29, 2012     www.energyefficientbuild.com

user swickedtwc said:
spreadsheet form pulling data form sheet1 to sheet 2 and calculating it 
HI,

I have a spreadsheet form that helps students define their learning style i.e Visual, Auditory, or Tactile. The data is collected in sheet1. I want to add calculations to each question and then sum the results identifying the students learning style. I have tried import rage in to a second spreadsheet which worked but did not update unless I had the sheets open. then I tried simply using sheet2 to pull the data from sheet1 and calculate it but the calculations change because of the way new answers to the form are inserted. So now I'm messing with =ARRAYFORMULA, but have not been able to get it all to work. I want sheet2 column1 row 2 and down to contain column AB row 2 and down.  I want a column for each question to contain a calculation that looks like =if(Sheet1!D2="Often",5, if(Sheet1!D2="Sometimes",3, if(Sheet1!D2="Seldom",1))) but then continues to grow as data is inserted. I tried this =ARRAYFORMULA(if(Sheet1!C2="Often",5, if(Sheet1!C2="Sometimes",3, if(Sheet1!C2="Seldom",1)))) and it gave me the right answer but did not grow to the row below it.

Thanks for any help you can be,

Chad

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

I don't have access to the user's spreadsheet(s) ... however, in the following I have provided a solution for the components that Chad would need for his real world project: