How to added numbers in a field if its a certain number

435 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

I am trying to create a survey. And the survey has 4 options for each question A B C D.

For A its 1, B is 2, C is 3, and D is 4. Each is summed for a total across all the fields in a record giving a total for each record.

What I want to do now is count the number of As, Bs, Cs and Ds across the record and I can’t seem to do that ;-(

Should be something like sum if Field 1 = 1 and Filed 2 = 1 and Field 3 =1 …

And then another with Sum if Field 1 = 2 and Filed 2 = 2 and Field 3 =2 …

And so on for each record so I get the total number of A, B, C and D.

But I cant seem to do that does anyone have any idea how I can do this?
Thanks in advance

1 Reply 1

Welcome to the community, @Pat_Connor! :grinning_face_with_big_eyes: The logic that you outlined is solid, but you then wrote, “I cant seem to do that,” and didn’t give an example of what you’ve tried so far.

Here’s how you can turn the logic that you wrote into a formula. To count all of the As, use this:

SUM({Field 1} = 1, {Field 2} = 1, {Field 3} = 1, ... )

{Field 1} = 1 will return a 1 or 0: 1 if {Field 1} contains a 1, and 0 if it doesn’t. Same for all of the other field comparisons. The SUM() function then adds all of those ones and zeroes to give you the total number of fields where A was the selected option. Duplicate this field three more times and change the comparison numbers to create fields that effectively count the Bs, Cs, and Ds.