Counting number of times Name appears in multiple contact column
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
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)
-
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,)
-
Thank you for your help. I figured it out. I put the criteria of fiscal year first.
-
-
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)
-
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.
-
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
-
@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)))
-
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.
-
@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?
-
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.
-
@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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.6K Get Help
- 63 Global Discussions
- 68 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!
