Countif with OR?
Hi All,
We have a department where the Project Managers can be assigned as the design PM and/or the construction PM. Please see the example below:
In this snapshot, I filtered the view to where D-PM is one of M. Johnson or C-PM is one of M. Johnson. In the D-PM column he is listed 3 times and the C-PM column he is listed 5 times.
I would like a formula where it returns the total number of projects he has worked on (6). I've tried COUNTIFS, OR statements, nested statements, etc... but I cannot seem to get it to return the correct number. There is another column (not shown here) for status that I would also like to use as a filter in the counting formula.
Any help would be much appreciated!
James
Answers
-
I would ride with a sum of COUNTIFS in your case.
=COUNTIFS([D-PM]:[D-PM], "M. Johnson") + COUNTIFS([C-PM]:[C-PM], "M. Johnson") - COUNTIFS([D-PM]:[D-PM], "M. Johnson", [C-PM]:[C-PM], "M. Johnson")
This should do the trick.
-
Try
=COUNTIFS([D-PM]:[D-PM], CONTAINS("M.Johnson", @cell), [C-PM]:[C-PM], CONTAINS("M.Johnson", @cell)) + COUNTIFS([D-PM]:[D-PM], CONTAINS("M.Johnson", @cell), [C-PM]:[C-PM], NOT(CONTAINS("M.Johnson", @cell))) + COUNTIFS([D-PM]:[D-PM], NOT(CONTAINS("M.Johnson", @cell)), [C-PM]:[C-PM], CONTAINS("M.Johnson", @cell))
What we're doing is counting if the name is present in both columns then if the name is present in only D-PM column and then if the name is present only in C-PM column. It'll give you 6.
-
That works! Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.3K Get Help
- 386 Global Discussions
- 212 Industry Talk
- 446 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 292 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!