Count The Frequency Of Collaborators


#1

Hi there,

I am looking to create an IF-THEN statement that allows me to count the frequency of the same collaborator in a table, count the number of times that same name appears and spit out instructions based on that number. Where I am getting stuck is the ability to count the frequency of the same name. Would appreciate any input. Thanks!


#2

This is possible to do, although the process is a bit of a kludge.

The biggest problem is, as far as I know, there is no easy way to create a universal formula for counting collaborators; instead, you have to create a formula that explicitly counts a specific collaborator and either handle each collaborator in his/her own field, combine all of the formulas in one giant IF() statement, or figure out some other way to handle the data.

First, the formula. I assume you have a rollup or lookup field that sums up a Collaborator field across multiple rows; I’ll call it {Collaborators}. For a given collaborator, the formula to count the number of times his or her name appears in {Collaborators} is this:

(LEN({Collaborators})-
LEN(SUBSTITUTE({Collaborators},'Collaborator Name','')))/
LEN('Collaborator Name')

What this does is takes the length of the string created by concatenating all the collaborators’ names and subtracting from it the length of the string containing all the collaborators’ names except the name you wish to count. The resulting difference in length is then divided by the length of that individual collaborator’s name, resulting in the number of times that name was found in {Collaborators}.

Rinse, repeat.

As I noted, such a calculation must be done for each collaborator individually — and, lacking any sort of looping ability in Airtable, leaves you to choose from a few less-than-optimal approaches. If I was trying to put something like this together, I would probably define an additional table containing a record for each collaborator, and substitute {Collaborator Name} for 'Collaborator Name' in the previous formula.

Hope this helps; let me know if anything was unclear, as this was written in the middle of a half-dozen emails and phone calls. :slight_smile:


#3

May want to check out the “search” block in the new airtable blocks. It allows you to search the entire base for keywords, and then returns matching records in a list. Although not sure what you can do with the results from there, other than display in a list.