Countif with OR?

jmilanese89091

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

David Joyeuse

Hi @jmilanese89091

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.

Ameya Athalye

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.

jmilanese89091

That works! Thank you!