nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

COUNTIF Formula

Sidonia Posteuca

Hello!

I am trying to set up a formula in my sheet summary that counts a cell if it includes one of three names. So far I have tried the following but am either getting 0 or an error message..

=COUNTIFS(OR(FIND("Name1", [ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC]), FIND("Name2", [ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC]), FIND("Name3", [ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC])))

=COUNTIF([ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC], "Name1") + COUNTIF([ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC], "Name2") + COUNTIF([ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC], "Name3")

I do not want to count any cells more than once.

Please help!

Accepted answers

Zachary Hall

That is why... This should help:

COUNTIF([ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC], FIND("Name1",@cell)>0)+COUNTIF([ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC], FIND("Name2",@cell)>0)+COUNTIF([ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC], FIND("Name3",@cell)>0)

Your names will have to appear exactly as they do in the contact column (which could also be their email address).

I hope this helps!

All comments

Zachary Hall

Do the cells that contain the names have multiple names in them? If so, are they contact cells or just normal text in a string format?

Sidonia Posteuca

Yes. It is a contact list column. Some cells have one name, some have two, some have three.

Zachary Hall

That is why... This should help:

COUNTIF([ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC], FIND("Name1",@cell)>0)+COUNTIF([ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC], FIND("Name2",@cell)>0)+COUNTIF([ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC], FIND("Name3",@cell)>0)

Your names will have to appear exactly as they do in the contact column (which could also be their email address).

I hope this helps!

Sidonia Posteuca

It worked. Thank you!

Will this @cell in the formula mess up any cell links in the sheet?

Zachary Hall

Nope! You should be good to go :)

Sign in to join the conversation!

Quick-Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.