COUNTIF Formula for Contact Column

John- Michael Diedrich

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.

John- Michael Diedrich

https://community.smartsheet.com/discussion/comment/376486#Comment_376486

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

Hollie Green

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

Nick Korna

Hi @John- Michael Diedrich,

This should work for you:

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

Hope this helps!

John- Michael Diedrich

https://community.smartsheet.com/discussion/comment/376441#Comment_376441

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?

John- Michael Diedrich

https://community.smartsheet.com/discussion/comment/376446#Comment_376446

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

Hollie Green

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

John- Michael Diedrich

https://community.smartsheet.com/discussion/comment/376457#Comment_376457

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

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

Nick Korna

https://community.smartsheet.com/discussion/comment/376450#Comment_376450

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.

John- Michael Diedrich

https://community.smartsheet.com/discussion/comment/376464#Comment_376464

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.

Hollie Green

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

Nick Korna

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!

John- Michael Diedrich

https://community.smartsheet.com/discussion/comment/376469#Comment_376469

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.

Hollie Green

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

John- Michael Diedrich

https://community.smartsheet.com/discussion/comment/376486#Comment_376486

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

John- Michael Diedrich

https://community.smartsheet.com/discussion/comment/376473#Comment_376473

Nick, you were right. thank you for the help here, this was very helpful.