Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Search in an array to count a specific string field

Topic Labels: Formulas
481 3
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi everyone! I’m working in a pretty big AirTable right now and I have a question regarding searching an array. So if you see the first column I have a Lookup field with a large amount of items, some repeated, and I would like to search for each item and count how many times it was in the record. So for example, we would like to see how many “Mechanic” strings were in the array pulled from “Users (from User Categories)”. I thought the first step would be to convert the lookup field to an array so I have “Archetype Array”. However, I can not figure out how to search “Archetype Array” for specific string insides of it, like “Mechanic”.

If you have any ideas to help, please let me know! Thanks!! :slightly_smiling_face:
Screenshot 2021-09-01 152727

3 Replies 3

You should have a Count field with conditions applied like {Field Name} = "Mechanic" to give you the count of that particular type. If you need to count all categories, you will probably need one count field per category. You could then use a formula field that concatenates all the values together.

Hello! I actually was not able to put a count field since the fields I want to count are lookup fields. I did find a workaround that involves making a new linked field from those lookup fields and then I was able to make a count field. But when I try to use the count field it will only return 1 when there should be more than 1. Let me know if you have any idea why that is, thanks!

Screen Shot 2021-09-02 at 9.11.02 AM

Screen Shot 2021-09-02 at 9.11.11 AM

You need to add the Counts to whatever table you’re looking up from, then add up the Counts in the table you’re screenshotting using Rollups SUM(values)