与多个列联系数行

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

What does your current formula return?<\/p>","bodyRaw":"[{\"insert\":\"What does your current formula return?\\n\"}]","format":"rich","dateInserted":"2020-10-01T18:50:02+00:00","insertUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-07-31T18:30:55+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/264509#Comment_264509","embedType":"quote"}"> 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 TutwilerThanks 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

No worries, I know it's tough when you're working on something.<\/p>

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

=SUM(COUNTIF(ProgMgr:ProgMgr, CONTAINS("Bob", @cell)), COUNTIF(ProjMgr:ProjMgr, CONTAINS("bob", @cell)), COUNTIF(ChMgr:ChMgr, CONTAINS("bob", @cell)))<\/p>

Could it be an issue with the references?<\/p>","bodyRaw":"[{\"insert\":\"No worries, I know it's tough when you're working on something.\\nThat's strange. I set up your columns in a local sheet on my Test sheet and did this formula and it worked:\\n=SUM(COUNTIF(ProgMgr:ProgMgr, CONTAINS(\\\"Bob\\\", @cell)), COUNTIF(ProjMgr:ProjMgr, CONTAINS(\\\"bob\\\", @cell)), COUNTIF(ChMgr:ChMgr, CONTAINS(\\\"bob\\\", @cell)))\\nCould it be an issue with the references?\\n\"}]","format":"rich","dateInserted":"2020-10-01T19:13:18+00:00","insertUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-07-31T18:30:55+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/264526#Comment_264526","embedType":"quote"}"> 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))