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

  • David Joyeuse
    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
    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.

  • That works! Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!