SUMIFS - How to include multiple "Criterion" in a "Range"

I am sure there is a simple solve but it doesn't seem to be working for me. I am trying to use the COUNTIFS formula, with 2 ranges in included. The first range ("Status") is straight forward as I only want to select one criterion ("In Progress). In the second range ("Division"), I want to select multiple criterion (i.e. "Men's", Women's, "Accessories"), so that I can get a count of all "In Progress" counts for All divisions. Here is what I have tried which I thought would work:

=COUNTIFS(Status:Status, "In Progress", Division:Division, "Men's", "Women's", "Accessories")

=COUNTIFS(Status:Status, "In Progress", Division:Division, "Men's", Division:Division, "Women's", Division:Division, "Accessories")

Hoping someone can help steer me in the right direction? Thanks!

Find more posts tagged with

Formulas

Accepted answers

All comments

Bassam Khalil

Hi @Grace ONeill

Hope you are fine, please try the following formula:

=IFERROR(COUNTIFS(Status:Status, @cell = "In Progress", Division:Division,
OR(HAS(@cell,"Men's"), HAS(@cell, "Women's"), HAS(@cell, "Accessories"))), "")

the following screenshot shows the result:

Grace ONeill

Hi Bassam,

Thank you so much for your response. Using that formula, I am still not getting the right answer for other ranges/criteron.

This is what I used:

=IFERROR(COUNTIFS(Status:Status, @cell = "Gap In Progress", Division:Division, OR(HAS(@cell, "Apparel - Men's"), HAS(@cell, "Apparel - Women's"), HAS(@cell, "Apparell - All"))), "")

Bassam Khalil

@Grace ONeill

If you like share me as an admin on a copy of your sheet after removing any sensitive data and I will check why it's not working with you.

Genevieve P.

https://community.smartsheet.com/discussion/comment/302896#Comment_302896

Hi @Grace ONeill

Can you identify what type of COUNT you're looking for, and how the Division column is set up? It looks like it's a multi-select column, is that correct? If so, do you want a cell that has both "Womens" and "Mens" to be counted once or twice?

Here's how I would set up this formula:

=COUNTIFS(Status:Status, @cell = "Gap In Progress", Division:Division, HAS(@cell, "Apparel - Men's")) + COUNTIFS(Status:Status, @cell = "Gap In Progress", Division:Division, HAS(@cell, "Apparel - Women's")) + COUNTIFS(Status:Status, @cell = "Gap In Progress", Division:Division, HAS(@cell, "Apparel - All"))

I would add together the three separate COUNTS. One for each Division. Would this work for you? If not, it would be helpful to see a screen capture of your sheet, but please block out sensitive data.

Cheers,

Genevieve

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.