Counting Rows with multiple contact columns

Danielle Roach

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😫

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

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

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:

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