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😫
=COUNTIF(OR({ProgMgr}, CONTAINS("Bob", @cell), {ProjMgr}, CONTAINS("bob", @cell), {ChMgr}, CONTAINS("bob", @cell))
Best Answer

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

What does your current formula return?

@David Tutwiler #Incorrect Argument

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

@David Tutwiler Thanks for the quick responses!
It returned 0

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?

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

@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...

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))
Help Article Resources
Categories
Check out the Formula Handbook template!