4

I have a column set up as a multiple contact to capture all authors on a journal publication. I would like to use a formula that will count the total number of times a specific name appears in then column. I have tried =COUNTIF({ALL AUTHORS},"John Doe"), but I know it does not yield all of the times the name appears. I doubled checked by using the filter feature. 

Also, is the a formula that can be used that would calculate the most number of times a name appears without calculating individually.Like listing authors in a 1st, 2nd, and 3rd type of rating?

I keep these statistics in a separate sheet, so I use the reference option within the formulas.

Thanks.

Beth

Functionality
Industry
Department

Comments

Because you are looking in a column with multiple contacts listed in various cells, using a specific name will not work. This is because it is looking for the cell to be an exact match. However...

 

The FIND function will look through a text string for a specific data point and return a NUMBER reflecting the first character position within the searched string of where the specified data starts. Looking for the letter "A" in the above paragraph would return the number of 4 because the first time it runs into the letter "A" is in the 4th position.

 

Likewise, you can use the FIND function to return the number of 9 by searching for "you" because the 9th position is the first character in the text you are searching for.

 

Having said that... We can use the FIND function to generate a number based on where within the string the text is found. It doesn't matter what that number is because if any number is generated, that means it exists within the cell. Other than the number zero. That means it wasn't found.

 

So using the FIND function within your criteria, we can say that if the number it returns is greater than zero (meaning the specific text exists within the cell), count it.

 

=COUNTIF({ALL AUTHORS}, FIND("John Doe", @cell) > 0)

In reply to by Paul Newcome

Thank you. That works beautifully. I am trying to find the best spot to add in a second criteria to look for a specific Fiscal Year, which is in another column. I have tried to put it before and after find, but it is giving me an incorrect argument. Any suggestions on better placement. 

 

=COUNTIFS({ALL AUTHORS}, {FISCAL YEAR}, "fy2019", FIND("jOHN dOE", @cell) > 0,)