Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Problem calculating percent of survey responses when no answer given

Topic Labels: Formulas
Solved
Jump to Solution
1865 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Rob_Jameson
6 - Interface Innovator
6 - Interface Innovator

Hi everyone!

I’m new to learning how to use the deeper aspects of Airtable and how to use linked records.

The problem I’m having … I have a multi-question survey, and am trying to calculate the percentage that people answered a question “yes”. However, some people skipped the question, so I need to remove these from the % calculation.

My goal is to determine what percentage of responses were “yes” from those who actually responded.

I tried by making a lookup table for each question that would only show answers yes/no (so no blank answers), and then the formula COUNTALL ({my lookup field name}) to first tell me how many responded, but I am not getting the correct answer.

Does anyone have recommendations for how to solve this?

Thank you

I

1 Solution

Accepted Solutions

Hi Rob,

This make much more sense now :slightly_smiling_face:

Actually since the Yes and No are linked records in the survey table, you can just make a Count field which will count all the linked records.

The formula field is basically counting the number of cells/field, which in your case is one :slightly_smiling_face: In the rollup field its counting the number of records that has a yes / no.

You do not need a lookup field, you need a linked field to use the rollup.

See Solution in Thread

6 Replies 6

hi @Rob_Jameson ,

Welcome to Airtable Community !

With the little data I have, I think what you are looking for is COUNTA() not COUNTALL()

COUNTA counts the cells that has value, COUNTALL counts all cells even if blank.

HOWEVER, since you are using a Lookup field, this means there are linked records. Why not use a Rollup field instead?

Thank you for the quick reply @Mohamed_Swellam! I appreciate it…

I had tried the COUNTA as well… the answer was not correct for some reason. I just says “1”… I am attaching screenshots… This is when I was doing it with a formula.

I then tried to use a Rollup on the new table and I was able to get the correct summary (thank you!). But I’m curious if this is what you meant to do? Is there a way to use the rollup without a lookup field? When I tried to do that in my original table, I could not select the field in the same table.

Here’s what I initially tried (incorrect):
Screen Shot 2022-08-11 at 11.40.00 AM
Screen Shot 2022-08-11 at 11.39.36 AM

Here’s using a rollup, but how can I do in my main table?
Screen Shot 2022-08-11 at 11.53.03 AM

Hi Rob,

This make much more sense now :slightly_smiling_face:

Actually since the Yes and No are linked records in the survey table, you can just make a Count field which will count all the linked records.

The formula field is basically counting the number of cells/field, which in your case is one :slightly_smiling_face: In the rollup field its counting the number of records that has a yes / no.

You do not need a lookup field, you need a linked field to use the rollup.

Fantastic… you have taught me much… Thank you @Mohamed_Swellam!

You’re welcome Rob, my pleasure !