Counting Rows with multiple contact columns

I need to know how to count a row if a specific user is in ANY of 3 contact columns in a specific row with the possibility of multiple users - HELP😫

Screen Shot 2020-10-01 at 12.34.49 PM.png

=COUNTIF(OR({ProgMgr}, CONTAINS("Bob", @cell), {ProjMgr}, CONTAINS("bob", @cell), {ChMgr}, CONTAINS("bob", @cell))

Find more posts tagged with

Accepted answers

David Tutwiler

Ah, so a unique count in the column. I feel like Distinct might help here, but I couldn't get it to work. You could do it by adding an IF and returning a 1 or 0 for the SUM. Like this:

=SUM(IF(COUNTIF([ProgMgr]:[ProgMgr], CONTAINS("Bob", @cell)) > 0, 1, 0), IF(COUNTIF([ProjMgr]:[ProjMgr], CONTAINS("Bob", @cell)) > 0, 1, 0), IF(COUNTIF([ChMgr]:[ChMgr], CONTAINS("Bob", @cell)) > 0, 1, 0))

All comments

David Tutwiler

What does your current formula return?

Danielle Roach

https://community.smartsheet.com/discussion/comment/264509#Comment_264509

@David Tutwiler #Incorrect Argument

David Tutwiler

Oh I see, my mistake. The OR in the COUNTIF isn't going to work and you're trying to OR a range.

I think you'll just need to sum your COUNTIF statements.

Something like:

=SUM(COUNTIF({ProgMgr}, CONTAINS("Bob", @cell)), COUNTIF({ProjMgr}, CONTAINS("bob", @cell)), COUNTIF({ChMgr}, CONTAINS("bob", @cell)))

Danielle Roach

@David Tutwiler Thanks for the quick responses!

It returned 0

David Tutwiler

No worries, I know it's tough when you're working on something.

That's strange. I set up your columns in a local sheet on my Test sheet and did this formula and it worked:

=SUM(COUNTIF(ProgMgr:ProgMgr, CONTAINS("Bob", @cell)), COUNTIF(ProjMgr:ProjMgr, CONTAINS("bob", @cell)), COUNTIF(ChMgr:ChMgr, CONTAINS("bob", @cell)))

Could it be an issue with the references?

Danielle Roach

https://community.smartsheet.com/discussion/comment/264526#Comment_264526

hmmmm.... let me check.... i'll get back to you. Thanks David

Danielle Roach

@David Tutwiler - okay I got it to work (spelling matters hehe), but for the ones who are on multiple roles in the same project, it's returning that as a value, so for example, sally has 5 instead of 3...

David Tutwiler

Ah, so a unique count in the column. I feel like Distinct might help here, but I couldn't get it to work. You could do it by adding an IF and returning a 1 or 0 for the SUM. Like this:

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.