Skip to main content
Solved

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

J
Forum|alt.badge.img+4

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.

Best answer by Alexey_Gusev

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

View original
    Did this topic help you find an answer to your question?

    4 replies

    A
    • Anonymous
    • 0 replies
    • February 22, 2022

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

    A
    Forum|alt.badge.img+17
    • Andy_Lin1
    • Inspiring
    • 183 replies
    • February 22, 2022

    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.

    Alexey_Gusev
    Forum|alt.badge.img+23
    • Alexey_Gusev
    • Brainy
    • 1155 replies
    • Answer
    • February 22, 2022

    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

    J
    Forum|alt.badge.img+4
    • Jordan-van-Berg
    • Author
    • New Participant
    • 4 replies
    • February 23, 2022
    Alexey_Gusev wrote:

    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!

    Reply


    Most helpful members this week

    Terms & Conditions