Skip to main content

How to Rollup on Multiple Select Text?- HELP D:

  • April 17, 2018
  • 2 replies
  • 63 views
A

Hi Airtable Geniuses,

I am having trouble with something and I am at a dead end. I will try to explain best I can.

The base in question is a tracker for my Sample/Demo Chairs that I send out to clients.

I am trying to create a snapshot of the data in my “Lending History”.

I have created a table that links to my “Lending History”. From there I have used rollup to find the total # “Opportunities MTD” and total # “Chairs sent out MTD” using SUM.

Using the same linked information, I would like to calculate how many times a specific chair was sent out mtd by NAME since I have many different types of chairs. I would like to rollup the “Lending History” then take the “Chair Name” (which is multiple select) and COUNTIF that name appears.

I see COUNTIF is not available. How do I get around this? Any ideas? Is it even possible? Do I have to use a straight formula? I am at a loss.

Any advice would be great!

    This topic has been closed for replies.

    2 replies

    W
    Forum|alt.badge.img+5
    • W_Vann_Hall
    • Inspiring
    • April 17, 2018

    Take a look at the formulas in the [Calc] table in the base referenced in this reply. Essentially, what you want to do is rollup all the chairs sent out into one long string (using ARRAYJOIN(), typically), and then, for each chair you wish to count, from the LEN() of that string subtract the length of that string with the name of the chair replaced by an empty string. (This is done with the SUBSTITUTE() function, as so: SUBSTITUTE({Long String},'Chair Name','').) Finally, divide the remainder by the length of 'Chair Name' and you’ll have the number of times the chair named {Chair Name} had been shipped.

    It’s far simpler than that tortured description suggests; examine the base, and you should quickly see how to modify the function for your own application.

    A
    • Amy_Aquino
    • Author
    • New Participant
    • April 20, 2018

    Take a look at the formulas in the [Calc] table in the base referenced in this reply. Essentially, what you want to do is rollup all the chairs sent out into one long string (using ARRAYJOIN(), typically), and then, for each chair you wish to count, from the LEN() of that string subtract the length of that string with the name of the chair replaced by an empty string. (This is done with the SUBSTITUTE() function, as so: SUBSTITUTE({Long String},'Chair Name','').) Finally, divide the remainder by the length of 'Chair Name' and you’ll have the number of times the chair named {Chair Name} had been shipped.

    It’s far simpler than that tortured description suggests; examine the base, and you should quickly see how to modify the function for your own application.


    Hi!

    Thank you for your reply! I have been looking over this and I am trying so hard to figure it out, but this is like elvish to me. Sounds pretty, but I do not understand.

    I made a copy of the base I built and took out the non-essential and private information. I’m trying to add this to the “Snapshot Report” tab. Would you be able to add the formula you are speaking about?

    No worries if not, I am thankful either way :blush:

    Terms & ConditionsAccessibility statement