How can I get the sum of a value in fields with single select multiple choice?

[Mike_D_RAG]

For example we do surveys to our schools, and they have multiple choices as to how to answer the questions. We want to pull data based on how many students answered a specific way to a specific question. We created the questions as fields and sent the forms out.

So like: “How often do you come to class prepared?” and the answers are “Never” “Sometimes” or “Often” and we would like grab what % of the students answered “Never” or “Often” etc…

End result would be a pie chart that has the questions and the % of students that answered “Often” Very Often" etc…

any help pulling the data as a sum or even a percentage would be AMAZING!

[Mohamed_Swella1]

Hi @Mike_D_RAG ,

Welcome to Airtable Community !

I think the best option here is to use the Extension - Pivot Table , it would summarize that for you.

[Mike_D_RAG]

Mohamed… No sir. Thre is no aspect of the pivot or pie that pulls the students answers and ties it to the questions asked giving the answers as percentages.

6 months of research and studying, I have tried alot of things. In this case, a simple answer will not work. I am looking for a script, or something of that nature.

[Mohamed_Swella1]

Well the other easy way would be to convert those Single Select fields into Linked Records fields , This way you can use Rollup fields to get the data that you need.

Although this might become a bit tricky (since I assume you have multiple questions that you need this data for and not only 1 question?)

What comes to mind is the following:

1. Create a new Linked Record field - Create New Table (its empty for now)
2. Using Automations, you will create a simple expression in the newly linked field, change the answer from “Never” to “How often do you come to class prepared? - Never” which will then be linked to the new table you created
3. In this new table, you can then use Rollups to get the counts

IF you dont want counts and want percentages instead, then you will do the same way but with a slight difference:

1. Each Question will then become a its own Linked Record (without adding the answer to the question name)
2. In the newly created table you need a separate field for each question and possible answer (so the field will become “How often do you come to class prepared? - Never” , using conditional Rollups, count the number of students that made that answer
3. Create a Count field to count all the linked records (total number of students answered that question, whatever the answer is)
4. Back to the field in Step 2, using a simple formula you can get the percentage

Hope this helps :slightly_smiling_face:

[Mike_D_RAG]

Thank you Mohamed, I have tried the roll ups using linked records and it is still not the desired outcome. your second suggestion doesnt produce percentages at all.

N E 1 got a script for what I am asking for?