Counting number of times Name appears in multiple contact column

E_Pieper

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

Paul Newcome

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)

E_Pieper

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,) 

E_Pieper

Thank you for your help. I figured it out. I put the criteria of fiscal year first.

Paul Newcome

Happy to help! 

Guangda Shi

Hi, Do you know why this formula is not working? The assigned to is a column of contact list...

=COUNTIFS({LR Date Created}, IFERROR(YEAR(@cell), 0) = 2020, {LR Date Created}, IFERROR(MONTH(@cell), 0) = Month@row, {Assigned To}, FIND(“Kamil”, @cell)>0)

ON

Not sure if this thread is still open but I have a similar issue to the OP.

I have a master project sheet (called Workplan) with an "Assigned to" column (multiple contacts per cell), A status column and a due date column.

In a separate sheet, I have the "user name" in [Assigned to], In the [Is Not Status] column I have the status "completed" and finally I have a column called [In the next (days)] where I enter the variable for the sum, in this case, "7" to return tasks in the next "7" days

My criteria:

Count the total number of rows with this "user name" where the status IS NOT "completed" and the due date is in the next "7" days.

My original formula works perfectly to count exact cell references of "user name" BUT where I have multiple entries in the [assigned to] column it doesn't count because it's looking for an absolute match...I've tried to use the FIND formula posted above with no additional criteria and it works. What I'm struggling with is adding the [Is Not Status] & [In the next (days)] Variables to the basic count.

ORIGINAL formula: (which works to count exact matches with set criteria, at the moment "1" as per screenshot it should be "2" - Using a sheet filter on the master sheet to confirm numbers)

=COUNTIFS({Workplan Assigned to}, $[Assigned to]$1, {Workplan Status}, <>[Is Not Status]@row, {Workplan Due Date}, AND(@cell >= TODAY(), @cell <= TODAY([In the next (days)]@row)))

Formula to count users in the multiple contact cell: (Works fine! Returns "421" :-) )

=COUNTIFS({Workplan Assigned to}, FIND($[Assigned to]$1, {Workplan Assigned to}, 0) > 0)

My attempt to Merge the two working formulas into one desired outcome:

REVISED formula: (With additional Variables does not work) The one I need to FIX

=COUNTIFS({Workplan Assigned to}, FIND($[Assigned to]$1, {Workplan Assigned to}, 0) > 0, {Workplan Status}, <>[Is Not Status]@row, {Workplan Due Date}, AND(@cell >= TODAY(), @cell <= TODAY([In the next (days)]@row)))

@Paul Newcome you posted the original reply to this post and you also helped me out on something else last week 😬 I'm hoping I haven't used up all your good graces... Any thoughts on the above would be most welcome.

ON

Update: The formula I thought was working - whilst it returns a value of "421", on cross-reference I realised that is incorrect. 421 is the total number of rows in the master Workplan sheet.

=COUNTIFS({Workplan Assigned to}, FIND($[Assigned to]$1, {Workplan Assigned to}, 0) > 0)

If I change the Absolute cell reference $[Assigned to]$1 to "Quoted text" of any kind username or a random word (I tried it with "Banana") it returns the same value of "421" 🤦‍♂️

I'm a little out of my league here, Any suggestions most welcome

Paul Newcome

@ON Try an @cell reference in your FIND function like so...

=COUNTIFS({Workplan Assigned to}, FIND($[Assigned to]$1, @cell) > 0, {Workplan Status}, <>[Is Not Status]@row, {Workplan Due Date}, AND(@cell >= TODAY(), @cell <= TODAY([In the next (days)]@row)))

22Wordsmith

https://community.smartsheet.com/discussion/comment/183401#Comment_183401

I have an Assigned To column with multiple entries and need to count the individual instances of names. However, I can't figure out the correct way to refer to this column to satisfy the Range requirement of the formula.

Paul Newcome

@22Wordsmith I'm not sure I follow. Which range exactly are you talking about? The cross sheet reference for the COUNTIF or the "@cell" for the FIND?

22Wordsmith

Hi @Paul Newcome I want to use your formula =COUNTIF(({ALL AUTHORS}, FIND("John Doe",@cell>0) in a smartsheet where the John Doe value is in a column titled Assigned To with multiple entries in each cell.

We were wanting to count how many rows were assigned to each individual in the team. Please don't hesitate to advise if there is a better way to do this, as I am learning on the go here.

Paul Newcome

@22Wordsmith It looks like you have some misplaced parenthesis.

Try removing one of the opening parenthesis immediately after the COUNTIF function and inserting a closing parenthesis immediately after the @cell reference.