# COUNTIF Formula for Contact Column

Options

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.

Tags:

Options

This worked! Thank you for the help, this was very helpful

• ✭✭✭✭✭✭
Options

Try =COUNTIF({contact},CONTAINS([Name(contact)]@row,@cell)

• ✭✭✭✭✭✭
Options

This should work for you:

=COUNTIF({Contact},HAS(@cell,[Name(contact)]@row))

Hope this helps!

• Options

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?

• Options

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

• ✭✭✭✭✭✭
edited 05/18/23
Options

@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.

• Options

It is giving me incorect argument set error for the below formula:

=COUNTIF({contact}, CONTAINS([Contact]@row))

• ✭✭✭✭✭✭
Options

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.

• Options

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.

• ✭✭✭✭✭✭
edited 05/18/23
Options

@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.

• ✭✭✭✭✭✭
edited 05/18/23
Options

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!

• Options

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.

• ✭✭✭✭✭✭
Options

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