Forum Discussion

JohannaEvans's avatar
JohannaEvans
Qrew Trainee
6 years ago

Formula to Summarize Multi Select Text by Group rather than Groupings


Hi QuickBase Friends,

I have a multi select text field that I need to summarize by group rather than by grouping.

I started the formula with the following: (I am getting a message expecting a textlist, which is what I thought I provided). I have lost myself in my own formula. Have others run into this?

var textlist counterif=textList(",",ToBoolean([Linked to Priority Strategic Aim]))
If (Contains([PC 1.0 Readmission rate],"PC 1.0 Readmission rate"),1,0),
If (Contains([PC 2.0 Hospital Acquired Infection rate],"PC 2.0 Hospital Acquired Infection rate"),1,0),
If (Contains([PC 3.0 Clinical Care Coordination],"PC 3.0 Clinical Care Coordination"),1,0),
Errors
Formulas and functions

17 Replies

Sort By
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    Can you explain further.

    Are you saying that you have a list of say 6 choices but in your mind some of them are Group A and some are Group B and you want to know how many A's and how many B's?
  • JohannaEvans's avatar
    JohannaEvans
    Qrew Trainee
    6 years ago

    I have a list of multi text selections that have about 25 items.

    When I summarize the fields, a table displays the selections by grouping:

    Item 1      = 1
    Item 1, 5  = 1
    Item 1, 3  = 1

    This is not what I am looking for, therefore, I thought I could write a formula that recognized the group rather than the grouping.

    Item 1 = 3
    Item 3 = 1
    Item 5 = 1

    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      6 years ago
      I realized that this is simpler than i thought

      Try this

      var text Selections = ToText([My Multi select field]);
      var text SearchTerm = "PC 1.0 Readmission rate";

      IF(Contains($Selections, $SearchTerm),1,0)

      Then if that works OK, just copy the field 24 times and replace the formula variable for the SearchTerm.


    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      6 years ago
      Are you trying to make 25 fields each to hold the count of a 1 if the multi select contains the nth choice and a 0 otherwise?
    • JohannaEvans's avatar
      JohannaEvans
      Qrew Trainee
      6 years ago
      Yes - of the 25 rows I want a count of how often item 1 is selected (in the example is 3), rather than item 1, then item 1 and 3 counted as a grouping, and item 1 and 5 as a grouping.