COUNTIF Formula for Contact Column
Hello Community,
I have a formula setup to count the number of times a contact is mentioned in another sheet, but it is not counting the rows with multiple contacts in the cell.
Is there any way we can workaround this to count every time the contact is mentioned, even when it's multiple contacts in a cell?
My current formula is this: =COUNTIF({contact}, [Name(Contact)]@row)
Please let me know if you have any questions. I am hoping this is an easy fix.
Best Answer
-
This worked! Thank you for the help, this was very helpful
Answers
-
Try =COUNTIF({contact},CONTAINS([Name(contact)]@row,@cell)
-
This should work for you:
=COUNTIF({Contact},HAS(@cell,[Name(contact)]@row))
Hope this helps!
-
Hey Hollie,
Thank you for the help here. I am not sure what you are referring to in the "@cell" portion of that. Is it referring to the range to count the contact in?
-
Hey Nick,
Thank you for the help here. Unfortunately that did not work. I tried using the below formula:
=COUNTIF({contact}, HAS({contact}, [(Contact)]@row))
-
@John- Michael Diedrich What error is it giving you?
The @cell is telling it to count each cell that falls within the range given for your countif formula that contains the criteria defined.
-
It is giving me incorect argument set error for the below formula:
=COUNTIF({contact}, CONTAINS([Contact]@row))
-
Is your contact for the lookup (i.e. [Name (contact)]) in a column with the type set to Contact? It should work if so but if it's text, then it won't work correctly.
-
Hey Nick,
Thanks again for the help. I used the below formula and am getting a result of 0:
=COUNTIF({contact}, HAS({contact}, [Contact]@row))
Please let me know if something is off.
-
@John- Michael Diedrich If there is an issue with even 1 cell of data it can cause the incorrect argument error. I have fixed many formulas with this and then looked at my blank cells to figure out where the error is in my data
Try =IFERROR(COUNTIF({contact},CONTAINS([Name(contact)]@row,@cell),"")
Also don't forget to check what type of column you have set up as they both need to be the same Contact or text if they are different it won't work.
-
The formula originally given should work
=COUNTIF({Contact},HAS(@cell,[Name(contact)]@row))
Example data:
Cross sheet look up:
If you're trying to do something different to this, let me know as I may have the wrong idea of what you're trying to accomplish!
-
Hey Hollie,
Thank you for the help. They are both contact columns, but the range column can have multiple contacts in each cell.
Here is the formula I setup (I am pasting the full formula) and it's giving me a result of 0, when it should be 9:
=COUNTIF({assigned FE names}, CONTAINS([FE Name (CIT Contact)]@row, {assigned FE names}))
Please let me know if I have anything wrong.
-
I did some testing and it will give a 0 with Contacts using the Contains formula. Go with the Has formula. I created a couple of sheets and tested it. The below should work.
=IFERROR(COUNTIF({Contact}, HAS(@cell, [Name(Contact)]@row)), "")
-
This worked! Thank you for the help, this was very helpful
-
Nick, you were right. thank you for the help here, this was very helpful.
Help Article Resources
Categories
Check out the Formula Handbook template!
