Friday, March 11, 2011

yogi_CountCheckBoxResponsesFromFormSubmittals

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com



kasumner said:
I am building a writing rubric form and corresponding spreadsheet. I am having trouble assigning a point value to each checked box to allow the total number of checked boxes for a section to render a total score on the spreadsheet. I have tried to use the "COUNTIF" formula but cannot figure out the correct syntax.
When I have 5 potential boxes for "organization" and a student scores in two areas, the two areas show in cell G2 of the spreadsheet. I would like to add a column next to it and have the program count the number of selections and assign a score. This is the formula I tried in cell H2 but it is not working. =COUNT(IF(ISTEXT(G2:text_A,text_B,text_C,text_D,text_E)))
This is the information in the cell which should have a value of two.
C. 4-6 complete sentences, D. 7 or more complete sentences

---------------------------------------------------------------------------------
In the ResponseTable in Google spreadsheet, the responses from CheckBoxes that are checked (tick marked) get all lumped up together and if 3rd and 4th CheckBoxes were checked in response to a question ... in the ResponseTable those will be shown as items C. and D.

In the following solution, I used the COUNTIF function to sum up the responded to CheckBoxes (out of 5), I am going to count how many of A., B., C., D., and E. are present in an answer. So here we go ...



The formula in cell H2 of sheet yogi_ECAW Raw Scores is:
=sum(countif(G2;{"*A.*","*B.*","*C.*","*D.*","*E.*"}))
this formula is then copied down to cells H3, H4, ...
---------------------------------------------------------------------------------

Kim (kasumner) commented ...
I finished formatting the cells. The new data points entered from the form are not entered onto a row with the formula; it creates a new row. How can I get the new data to enter onto a row with the formulas?


Kim, for that we will need an arrayformula ... so I have added the sheet yogi2_ECAW Raw Scores. I added a blank row 2 and I added the following formula in cell H2 ...

=ArrayFormula(if(len(G2:G),mmult(iferror(sign(find({"A.","B.","C.","D.","E.","F."},G2:G))),sign(transpose(column(A1:F1)))),iferror(1/0)))

I added the formula in cell H2 of a blank row, so the formula will apply even to the first Form submittal  logged in the Response Table.