Google Spreadsheet Post #893
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Nov 25, 2012
user JesseBas said:(http://productforums.google.com/forum/?zx=jnm1y5sz2w47#!category-topic/docs/spreadsheets/I8VaQVYGDos)
How to count data on a Google Spreadsheet based on an identifier
Hi there, I'm trying to figure out how to count data on a Google Spreadsheet based on an identifier (a participant's email address). The spreadsheet was generated by Google Forms. The form first asks the participant email, and the asks them if they completed a number of tasks (some tasks will be completed more than once) . Some of the questions are checkboxes and others are comment boxes, I only need to count the checkboxes. What I need is a formula that will search through every column of the spreadsheet, and add to the count only if a participant's email matches the row with their name, and if they completed that particular task. On a new sheet (same Google Spreadsheet file however), every participant will have a row to themselves, and the columns will consist of the tasks that may or may not have been completed. For example, ja...@google.com completed task x and y twice but forgot about task z. Therefore in the row titled "Jane" there would be a 2 in column x and y but a 0 in column Z. I believe this is possible with a COUNTIF function with multiple criteria but I heard that this isn't available on Google Spreadsheets. Does anyone know how to do this? Any help would be much appreciated, thanks,
- Jesse
---
Hi yogia,
Here is the link to the Google Spreadsheet:
https://docs.google.com/
For reasons of confidentiality, it is not the original file; it's a copy where I've deleted all the participants and created 3 "test" names.
The purpose of the form/spreadsheet is to evaluate the history taking skills of medical students in practice sessions. The "Observer" checks off and comments on the tasks completed by the medical student in the presence of a mock patient. Once the Observer submits the form, a script automatically sends it to the student's email. However, after multiple practice sessions, we want to be able to send out summed data so that the student knows what tasks he/she is consistently missing.
This summed data will appear on the sheet titled "Summed Data". Every student will have a row to themselves and the columns will consist of every checkbox for every task. So far I've only added the "Introduction" checkboxes, which are: "Introduces Self", "Shakes Hand", "Patient ID", "Chief Concern". There is also an attendance column that counts the total number of entries that contains that particular students email address (as this same spreadsheet will be used for multiple practice sessions). Note also that after the first session the order of the email addresses will change, as it depends on the order that the Observers submit the form. The formulas that should count the tasks are in the "Summed Data" sheet, columns D-G. I have experimented with some formulas, so far with no success.
Please let me know if you have any questions and are able to help. Thanks again,
- Jesse
-----------------------------------------------------------------------------------------------------
following is a solution to the problem
How to count data on a Google Spreadsheet based on an identifier
Hi there, I'm trying to figure out how to count data on a Google Spreadsheet based on an identifier (a participant's email address). The spreadsheet was generated by Google Forms. The form first asks the participant email, and the asks them if they completed a number of tasks (some tasks will be completed more than once) . Some of the questions are checkboxes and others are comment boxes, I only need to count the checkboxes. What I need is a formula that will search through every column of the spreadsheet, and add to the count only if a participant's email matches the row with their name, and if they completed that particular task. On a new sheet (same Google Spreadsheet file however), every participant will have a row to themselves, and the columns will consist of the tasks that may or may not have been completed. For example, ja...@google.com completed task x and y twice but forgot about task z. Therefore in the row titled "Jane" there would be a 2 in column x and y but a 0 in column Z. I believe this is possible with a COUNTIF function with multiple criteria but I heard that this isn't available on Google Spreadsheets. Does anyone know how to do this? Any help would be much appreciated, thanks,
- Jesse
---
Hi yogia,
Here is the link to the Google Spreadsheet:
https://docs.google.com/
For reasons of confidentiality, it is not the original file; it's a copy where I've deleted all the participants and created 3 "test" names.
The purpose of the form/spreadsheet is to evaluate the history taking skills of medical students in practice sessions. The "Observer" checks off and comments on the tasks completed by the medical student in the presence of a mock patient. Once the Observer submits the form, a script automatically sends it to the student's email. However, after multiple practice sessions, we want to be able to send out summed data so that the student knows what tasks he/she is consistently missing.
This summed data will appear on the sheet titled "Summed Data". Every student will have a row to themselves and the columns will consist of every checkbox for every task. So far I've only added the "Introduction" checkboxes, which are: "Introduces Self", "Shakes Hand", "Patient ID", "Chief Concern". There is also an attendance column that counts the total number of entries that contains that particular students email address (as this same spreadsheet will be used for multiple practice sessions). Note also that after the first session the order of the email addresses will change, as it depends on the order that the Observers submit the form. The formulas that should count the tasks are in the "Summed Data" sheet, columns D-G. I have experimented with some formulas, so far with no success.
Please let me know if you have any questions and are able to help. Thanks again,
- Jesse
-----------------------------------------------------------------------------------------------------
following is a solution to the problem
No comments:
Post a Comment