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

Find more posts tagged with

Accepted answers

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.