Problem calculating percent of survey responses when no answer given

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

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


Here’s using a rollup, but how can I do in my main table?

Hi Rob,

This make much more sense now :slight_smile:

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 :slight_smile: 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!

1 Like

You’re welcome Rob, my pleasure !

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.