Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Total number of each type of answer in every record

Topic Labels: Scripting extentions
Solved
Jump to Solution
290 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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