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

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

Solved
Jump to Solution
473 4
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

1 Solution

Accepted Solutions

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

See Solution in Thread

4 Replies 4

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

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.

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

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

THANK YOU!