Forum Discussion

BlancaAnderson's avatar
BlancaAnderson
Qrew Assistant Captain
6 years ago

Counting Varies Depending on Multiple Choice Selected

I have a multiple choice field that displays the following:

-NONE
-A
-B
-C
-D

I need a new formula field that displays "0" if NONE is selected and then if any other letters are selected to add them up.

So if unit

101 needs BD = 2
102 NONE = 0
103 AD = 2
104 C =1

Please help me with the formula.  I tried looking in the knowleadgebase but didn't see a case where if a certain selection in my case NONE would equal to "0" and count if any others.

I can provide screenshots if needed.
  • It was unclear, but I think you are counting the number of characters in the multiple choice. Am I correct?
    If so, this numeric formula might do it:
    If(
         [Letters Field] = "NONE", 0,
         Length([Letters Field])
    )
  • BlancaAnderson's avatar
    BlancaAnderson
    Qrew Assistant Captain
    That is correct, I want to count each letter as +1 only.  If NONE is selected then it should say 0

    I tried this:
    If([Tubs Needed]="NONE",0,Length([Tubs Needed]))

    and received this error on the = sign

    The operator '=' can't be applied to on types textlists text

    I am using a Formula Text field.  


    • JustinTorrence's avatar
      JustinTorrence
      Qrew Cadet
      Instead of [Tubs Needed]="NONE", try this:
      Contains([Tubs Needed], "NONE")
  • I'd suggest using a numeric formula field if possible because I think you're looking for a number as the output of your formula.  If you wanted to keep using a text formula field you might be able to use this formula based on what you provided:

    If([Tubs Needed]="NONE","0",ToText(Length([Tubs Needed])))

    Though for reporting purposes a numeric field seems like the way to go
    • BlancaAnderson's avatar
      BlancaAnderson
      Qrew Assistant Captain
      As you can see, the formula "sort of works" so we are closer :)
    • BlancaAnderson's avatar
      BlancaAnderson
      Qrew Assistant Captain
      Screenshot #1 
      Is the [Tub Count] Formula Field, it is a Formula Numeric Field - you can see the current formula I am using

      Screenshot #2 
      This is what the Tub Count is summarizing/counting.  If user selects NONE then I need 0, any others are selected then I need a count.  

      Screenshot #3
      The report, the last two columns on the right, Tubs Needed and Tub Count.  The NONE argument returns a 0, YAY! This works just as I want,  the second argument should COUNT every letter, instead I get a number 3 no matter how many letters are selected.  

      Please help.
    • BlancaAnderson's avatar
      BlancaAnderson
      Qrew Assistant Captain
      Yes, I tried but, I may be doing something wrong.  It returns this error.  I thought maybe because it is not expecting the multiple choice textlist?
    • JustinTorrence's avatar
      JustinTorrence
      Qrew Cadet
      Because you are using a text list, you have to do some mapping.
      Try this:

      var number a = if(contains([Tubs Needed], "A"),1,0);
      var number b = if(contains([Tubs Needed], "B"),1,0);
      var number c = if(contains([Tubs Needed], "C"),1,0);
      var number d = if(contains([Tubs Needed], "D"),1,0);
      If(
          contains([Tubs Needed], "NONE"),0,
          $a + $b + $c + $d
      )
    • BlancaAnderson's avatar
      BlancaAnderson
      Qrew Assistant Captain
      GENIUS! That worked :)
      Thank you so much, especially for your patience!