Count (sum) total number of YES for different fields in a separate field

Ask the community
#1

I would like to keep a total score in a separate field based on certain values in 1 to 10 fields. So let’s say:

Field 1: Yes
Field 2: No
Field 3: Yes
Field 4: No
Field 5: Yes
Field 6: Yes
Field 7: No
Field 8: Yes
Field 9: Yes
Field 10: Yes

Then a summarized 7 must be placed in the final number field. 7x yes = 7 and if I change field 3 to no after 2 weeks in the database, then field must become final grade 6 without having to do this manually.

Is this possible and if yes how? I read about rollup/formula fields but how to do this on these 10 examples fields? It should be all present in 1 table and not different tables.

#2

Rollup is the right way but if you share a screenshot of the base, we can see how to incorporate it where you want.

#3

The quick and dirty way would be a formula field with the following:

IF({Field 1}="Yes",1,0)+
IF({Field 2}="Yes",1,0)+
IF({Field 3}="Yes",1,0)+
...

There’s no equivalent to Excel’s SUMPRODUCT (since you can’t construct an array on the fly), so this is also the easiest to maintain when you need to add or subtract fields in the calculation.
Note that if you use a checkbox field for those fields (instead of Yes/No), you can just add them (i.e. {Field 1}+{Field 2}+...), because the value of checkbox fields is 1 for checked and 0 for unchecked.

#4

That’s quite easy
1st way:
({Field 1}=‘Yes’)+({Field 2}=‘Yes’)+({Field 3}=‘Yes’)…

2st way ( if you are sure it’s ‘Yes’ or ‘No’ and nothing else)
LEN(
CONCATENATE({Field 1},{Field 2}…{Field 10})
)-20

#5

WOW you made my day! I used the formula field this way and it works exactly as what I tried to do.

THANK YOU!

#6

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.