Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Total number of each type of answer in every record

Topic Labels: Scripting extentions
Solved
Jump to Solution
1451 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Darryl_Oliver
6 - Interface Innovator
6 - Interface Innovator

I’m using a base to record survey answers. The questions are constructed in a way that all answers are Yes/No. I’m using a single select field for the answers and I want two fields that will provide the total number of “Yes” answers and the total number “No” answers for each record.

I realize I can create two additional fields for every question that will store numeric values for each answer choice and then use two SUM fields to provide a total for each type of answer, however this will result in two additional fields for every question. I’m hoping someone has an idea for a more elegant solution; most likely involving a script.

1 Solution

Accepted Solutions

i agree with kamille. also heres a formula to look at that as a ratio (only use one field)

SUM(
   IF({Q 1} = "Yes", 1, 0),
   IF({Q 2} = "Yes", 1, 0),
   IF({Q 3} = "Yes", 1, 0),
   IF({Q 4} = "Yes", 1, 0),
   IF({Q 5} = "Yes", 1, 0)
)& ":"& SUM(
   IF({Q 1} = "No", 1, 0),
   IF({Q 2} = "No", 1, 0),
   IF({Q 3} = "No", 1, 0),
   IF({Q 4} = "No", 1, 0),
   IF({Q 5} = "No", 1, 0)
)

or if you need to see which questions were answered how?

IF({Q 1} = "Yes", '🟢', '🔴')&
IF({Q 2} = "Yes", '🟢', '🔴')&
IF({Q 3} = "Yes", '🟢', '🔴')&
IF({Q 4} = "Yes", '🟢', '🔴')&
IF({Q 5} = "Yes", '🟢', '🔴') 

image

See Solution in Thread

2 Replies 2

You could just use a total of two formula fields that each do something like this:

SUM(
   IF({Question 1} = "Yes", 1, 0),
   IF({Question 2} = "Yes", 1, 0),
   IF({Question 3} = "Yes", 1, 0),
   IF({Question 4} = "Yes", 1, 0),
   IF({Question 5} = "Yes", 1, 0)
)

i agree with kamille. also heres a formula to look at that as a ratio (only use one field)

SUM(
   IF({Q 1} = "Yes", 1, 0),
   IF({Q 2} = "Yes", 1, 0),
   IF({Q 3} = "Yes", 1, 0),
   IF({Q 4} = "Yes", 1, 0),
   IF({Q 5} = "Yes", 1, 0)
)& ":"& SUM(
   IF({Q 1} = "No", 1, 0),
   IF({Q 2} = "No", 1, 0),
   IF({Q 3} = "No", 1, 0),
   IF({Q 4} = "No", 1, 0),
   IF({Q 5} = "No", 1, 0)
)

or if you need to see which questions were answered how?

IF({Q 1} = "Yes", '🟢', '🔴')&
IF({Q 2} = "Yes", '🟢', '🔴')&
IF({Q 3} = "Yes", '🟢', '🔴')&
IF({Q 4} = "Yes", '🟢', '🔴')&
IF({Q 5} = "Yes", '🟢', '🔴') 

image