Sunday, October 12, 2014

yogi_Row By Row Summation Of Numerical Values Assigned To Alpha Entries In Google Form Responses

                  Google Spreadsheet   Post  #1788
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct-12-2014
post by  SK421:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Pd5-BFSwz9U)
Using arrayformula with countif on a google form
I created a form that populates a spreadsheet with multiple choice answers for an assessment. The assessment answers range from A. None, to E: Very High.
The assessment requires a number be given to every answer A. None =1, B. Low =2, C. Moderate=3, etc....  and a total needs to be calculated.
When I plug in =(COUNTIF(F2:M2,"A. None")+COUNTIF(F2:M2,"B. Low")*2+countif(F2:M2,"C. Moderate")*3+Countif(F2:M2,"D. High")*4+COUNTIF(F2:M2,"E. Very High")*5)
it calculates the answer that I want for the first row.
How do I use arrayformula to automatically calculate the totals for new form entries?
---
Here is a copy of the spreadsheet (the original has some client sensitive information in it.)

What I need is for column N to automatically calculate the total as new form entries are entered.
I was trying to use arrayformula, but I guess I don't really know what I'm doing.
I also added a key at the bottom.

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