# Total number of each type of answer in every record

Topic Labels: Scripting extentions
Solved
369 2
cancel
Showing results for
Did you mean:  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  9 - Sun

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", '🟢', '🔴')
`````` 2 Replies 2  16 - Uranus

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)
)
``````  9 - Sun

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