#CONTACT EXPECTED

Radoslav Ehlenov

Hi All, I am trying to make smth in Smartsheet work; tried everything but it doesn't work. In a sheet summary, I am trying to count how many times a name appears in a Contact list column. I use following formula: =COUNTIF(Owner:Owner, "name@email.com") but it throughs following error message: #CONTACT EXPECTED. I also tried with the Name instead of the email. I suppose it's smth with the Contact column as the same works fine with Text/Number columns. Would you have an idea how to make it work?

Andrée Starå

Hi @Radoslav Ehlenov

I’d recommend creating another field in the Sheet Summary (Contact) and select the contact that you want to use the formula for and reference that field instead in your formula.

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Radoslav Ehlenov

Hi @Andrée Starå

thanks for the fast answer!

I could follow your guidnace up to creating another Contact field in the Sheet Summary and selecting an existing contact but I didn't get "reference that field instead in your formula".

Finally, I am trying to count number of times this specific contact apears in the Contact column "Owner".

May you please elaborate a bit further?

Many thanks!

BR,

Rado

Andrée Starå

@Radoslav Ehlenov

Happy to help!

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

Radoslav Ehlenov

Hi @Andrée Starå

it would be pretty big effort to make the sheet ready for external sharing. It's a company asset; I beleive it's not a good idea to send it out.

Any chance that we can connect via Skype or smth and I share my screen?

Regards,

Rado

Andrée Starå

@Radoslav Ehlenov

Ok.

No worries!

To clarify, you'd reference the newly created Sheet Summary field in your formula so it would look something like this.

=COUNTIF(Owner:Owner, Contact#)

Contact# = The contact field we created in the sheet summary.

Make sense?

I'd be happy to connect!

Please send me an email at andree@workbold.com, and we'll take it from there.

Radoslav Ehlenov

Hi @Andrée Starå

yes, almost there...just struggling with referencing this new Contact# field in the formula of the other field.

How do I find out the identifier of this Contact# field?

I tried copy/pasting the Contact name from there or use Count#54 or Count54 as it is the 54th field in the sheet summary but negative.

BR,

Rado

Andrée Starå

@Radoslav Ehlenov

Make sure that the Field in the Sheet Summary is set to Contact, and then you'd type in the email and should auto-populate.

Did that work?

Radoslav Ehlenov

Hi @Andrée Starå

yes, this I did, it auto-populated the contact but now I need to reference this contact or the sheet summary contact field in the formula =COUNTIF(Owner:Owner, Contact#) but I don't find out what exactly Contact# is.

You said, it is the contact field we just created and populated with the contact name but what I should put under Contact# in the formula?

Copy/ paste of the populated name doesn't work, nor Contact#54. That's why I am wondering if there is a kind of identifier for this field to be refrenced in the formula?

BR,

Rado

Andrée Starå

@Radoslav Ehlenov

Make sure that the following is true.

• Owner = Contact List
• Contact# = Contact List and name of the Sheet Summary Field.

Radoslav Ehlenov

Hi @Andrée Starå

I ensured:

• Owner = Contact List - is TRUE
• Contact# = Contact List and name of the Sheet Summary Field - is TRUE and named the field Name

The formula now looks like this: =COUNTIF(Owner:Owner, Name) result #UNPERSEABLE

If I chnage it to =COUNTIF(Owner:Owner, "Name") result is #CONTACT EXPECTED

So, the workaround is not really working for me.

It's still strange that the original formula is not recognizing a contact in the Owner Contact List column.

I also tried to "clean up" the Owner Contact list column by removing cell linking, resource mgmt; also added a contact in the column setup form. Nothing helps.

Hope there will be other ideas :)

BR,

Rado

Andrée Starå

@Radoslav Ehlenov

The formula should be. =COUNTIF(Owner:Owner, Name#)

Is the field in Sheet Summary named Name?

Can you share a screenshot?

Radoslav Ehlenov

Hi @Andrée Starå

guess what, I just had an idea and it worked! :)

Maybe, this was the only detail I didn't share becuase I didn't think of it at all.

The sheet summary field where I was putting the formula was also a Contact field.

I changed it to Text/ Number field and used the exact contact name from the Owner column.

Using the email address would not work.

So, this is how I made it work.

Thank you so much for you support; solution was born in the discussion!

Have a great day!

BR,

Rado

Andrée Starå

@Radoslav Ehlenov

Haha! How could I miss that! 🤣

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!