Total number of each type of answer in every record

[Darryl_Oliver]

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.

[Kamille_Parks]

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

[Rebecca_Elam · Accepted solution]

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", '🟢', '🔴')