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

Options

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")

or

=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!

Tags:

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    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:


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Grace ONeill
    Options

    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
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @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.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 08/27/21
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!