Monday, September 1, 2014

yogi_Compute Row By Row For Each Person Number Of Tasks Not Done By Each Task

                      Google Spreadsheet   Post  #1746
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Sep-01-2014
post by Leroy S:
Count responses (text) and summarise over multiple rows and columns

first let me try to explain, in short, what I am trying to accomplish: I am making a task-report system for a local football club. At the end of every week, they fill in a google-form in wich they state whether or not a certain task was done or not the day before. It is kind of a check-up system. This part works fine, but it generates a lot of data which needs to be sorted. One thing we would like to see or summarise, is the amount of times a certain person forgot to do a certain task.

The data is organized in this fashion:

person adonedonedone
person bdonenot donedone
person anot donedonedone
person cdonedonedone
person anot donedonenot done

What I would like, is to count the number of times  certain people forgot to do certain tasks. For the example above, this would look something like this:

person a201
person b010
person c000

I have been trying to get this to work trough a query, but did not quite succeed (= QUERY(Formulierreacties!D:P ; "select P, count(E) where E='not done' group by P")). One of the difficulties i am facing is the fact that the to be counted values are not numerical. However, I was unable to convert the text responses (check-boxes) in google forms to numerical values in an easy way.

I hope someone can help me sort this out ;)

Kind regards,