Count the instances a word appears throughout a table

[Danielle_Moser]

I’ve got a staff list that also has columns for who their supervisor is, who reports to them, and who their teammates are. I need to count the number of times the staff member’s name appears anywhere on the table. Can you help me craft that formula?

[ScottWorld]

Welcome to the community, @Danielle_Moser!

Anywhere in the entire table? You can just hit command-F on Mac (control-F on Windows) to do a “Find” for the word you’re looking for, and Airtable will give you a count of the number of times that word appears anywhere in your current list of records. It will also highlight the word throughout your table as well.

[Bill_French]

Danielle_Moser:

I need to count the number of times the staff member’s name appears anywhere on the table. Can you help me craft that formula?

ScottWorld:

You can just hit command-F on Mac (control-F on Windows) to do a “Find” for the word you’re looking for

Given that @Danielle_Moser is asking about a formula, it sounds like the requirement calls for the creation of a sustainable (automated) metric - literally analytics about Each staff member’s activity.

Given a collection of field names and A specific staff member name, a formula field could test for Such existence resulting in a true/false data point. But how would you do this for a list of staff members? One metric formula/field for each staff member? That gets ugly pretty quickly and it doesn’t scale and it’s somewhat hard-coded (I.e., brittle and rigid).

I think the outcome is grid of data - staff member (in rows) with counts (in a column).

In my opinion - programming biased that it may be - I think this is best addressed through a script block that when run, performs a deep and comprehensive analysis of the data. A formula that does this might be possible, but there might be a few gotcha’s where inspections of fields may contain or equal the staff member’s name.

And if the goal is to track activity more deeply - such as knowing who created or updated data - the script would have visibility about activity where the staff member’s name doesn’t actually appear in a record but was created by various people of interest.

[Kamille_Parks]

Is the {Supervisor} field a LinkToAnotherRecord field (same question for the other two)? If your setup involves multiple LinkToAnotherRecord fields you could use 3 Count fields: one for the supervisor link, one for the reported to link, and one for the teammates link. Then a Formula field could add those three Counts together.

If you need to look for someone’s name in other field types you’ll need a more advanced solution as suggested.

[Danielle_Moser]

Yes, they all “link to” the Primary Field ({staff names}). However, if I use “count” it only counts in that row, and I need it to count throughout the document. And I would have to make a separate count for every line calling out a particular name, but the Count function blankets the whole column, so would only look for one name.

[Danielle_Moser]

Great! What’s a script block?

The overall goal: tracking 360 reviews and how many times in each month a staff member would have to do one. With 360, a person’s colleagues, supervisor, and direct reports all have to fill out a billion questions, and we are evaluating whether it makes sense to implement within our company, or if it would be too much work. So, I’m trying to count how many times a staff member’s name comes up in the entire table so I know how many times they’d be called upon to do a review. :slightly_smiling_face:

[Bill_French]

Danielle_Moser:

What’s a script block?

Well, it’s a way to program complex tasks that typically are not possible in basic formula fields.

Danielle_Moser:

how many times a staff member’s name comes up in the entire table so I know how many times they’d be called upon to do a review.

Understood.

My recommendation - if possible, offer to share access to a Base with some of this sample data (or even just a CSV export that could be imported to look like your real data). Then, I’m sure a few script block experts in the community could take a stab at a non-trivial demonstration of how it might work. I may have a few cycles to try this because it’s an interesting challenge.