Using COUNTIFS and OR statement

You're all so amazingly helpful that I'm back. I think this is an easy fix for most, but I'm scratching my head.

I have a column named Status with multiple drop down options, of which only one can be used. I also have a column named PMO that is a checkmark.

I want to get a count of PMO flagged rows that fall within several statuses (planning, execution, monitoring). The formula below works for one status - how do I sneak in the OR to pick up each of those three?

=COUNTIFS(Status:Status, "Planning", PMO:PMO, 1)

Thanks!

Find more posts tagged with

Formulas

Accepted answers

Jeff Reisman

@ErinM You are on the right track with OR.

=COUNTIFS(PMO:PMO, 1, Status:Status, OR(@cell = "planning", @cell = "execution", @cell = "monitoring"))

All comments

Jeff Reisman

@ErinM You are on the right track with OR.

=COUNTIFS(PMO:PMO, 1, Status:Status, OR(@cell = "planning", @cell = "execution", @cell = "monitoring"))

ErinM

Thank you so much, @Jeff Reisman - that worked! How on Earth do you all know how to do this stuff so well?!?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/364271#Comment_364271

😂 4 years experience with it, I find it interesting, and I have one of those inductive-thinking brains (ADHD superpower), plus solving small problems like this keeps the dopamine coming. 🤪

Jeff Reisman

@ErinM I think you've accidentally made this post three times. Might want to edit the others to erase the text and mark as duplicate.

ErinM

Ugh, yes, I was having an issue earlier. I'll fix that, thank you!

So, @Jeff Reisman - I'm trying to marry two different types of formulae that I learned through the community's help!

Two dropdowns, each with one selection option only. I want the Sponsor Division to contain "OneIT" and choose from one of the statuses in Status. Thoughts on why this is breaking?

=COUNTIFS([Sponsor Division]:[Sponsor Division], CONTAINS("OneIT", @cell), Status:Status, (@cell = "planning", @cell = "execution", @cell = "monitoring", @cell = "hold"))

Jeff Reisman

@ErinM I typed the below and then looked closer: You left the OR out!

=COUNTIFS([Sponsor Division]:[Sponsor Division], CONTAINS("OneIT", @cell), Status:Status, OR(@cell = "planning", @cell = "execution", @cell = "monitoring", @cell = "hold"))

Are you getting an error message (and if so, which one,) or just a 0 count when you know the values are there?

Is the value you're looking for in Sponsor Division column exactly = "OneIT", or is "OneIT" just a part of the cell value? CONTAINS searches for string values within cells, so it would match both "OneIT" and "OneIT-TwoIT-ThreeIT-Four". If you're only interested in cells where the value equals "OneIT" exactly, you don't need CONTAINS.

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.