Friday, March 11, 2011


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

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:
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 ...


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.