Combining multiple criteria in COUNTIFS formula

caw98776
caw98776 ✭✭
edited 12/09/19 in Formulas and Functions

There seems to be an error in my formula.  The first part works by itself (counting the number of times the SME's name appears).  Then, when I add the critieria to limit the count to those whose status is "Red", I get an "incorrect argument" error.  It is selecting the correct data sets to pull from.  Please look at my formula and tell me if you can identify a missing comma, parenthesis, or operator.  The formula is:

=COUNTIFS(SMEA:SMEC, = "Jane Doe", Status:Status, = "Red")

 

Tags:

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    I wonder if its because your first range has multiple columns and your second range doesn't match. Ttry setting it to =COUNTIFS(SMEA:SMEA, ="Jane Doe", Status:Status, = "Red")

    Does it work then? 

  • caw98776
    caw98776 ✭✭
    edited 08/05/19

    It does BUT I only receive 1/3 the hits I should because the name could appear in multiple columns.  Is there a way to make it repeat the count for each column and then add them together?

     

     

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Christie,

    You'd have to use the FIND function for it to work.

    I'll get back to the post later if you've not solved it before.

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • I'd appreciate that.  I thought perhaps I could repeat the formula three times and then have it add up but, since I've been using smartsheets a total of one day, I can't figure out how to do nested formulas and no examples seem to be available.  There's a seminar but I need something I can refer back to.

     

    Thanks!

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Andree or Mike will likely have a better formula, but to answer you other question, you can stack them like this:

     

    =COUNTIFS(SMEA:SMEA, "Jane Doe", Status:Status, "Red") + COUNTIFS(SMEB:SMEB, "Jane Doe", Status:Status, "Red") + COUNTIFS(SMEC:SMEC, "Jane Doe", Status:Status, "Red")

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Good job Nick. I was overcomplicating the matter trying to use @cell references embedded in an OR statement. Haha. That would be the right formula to use. 

  • Genius!  It works!smiley

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Try something like this.

    =COUNTIFS(SMEA:SMEA; FIND("Jane Doe"; @cell) > 0; Status:Status; "Red") + COUNTIFS(SMEB:SMEB; FIND("Jane Doe"; @cell) > 0; Status:Status; "Red") + COUNTIFS(SMEC:SMEC; FIND("Jane Doe"; @cell) > 0; Status:Status; "Red")

    The same version but with the below changes for your and others convenience.

    =COUNTIFS(SMEA:SMEA, FIND("Jane Doe", @cell) > 0, Status:Status, "Red") + COUNTIFS(SMEB:SMEB, FIND("Jane Doe", @cell) > 0, Status:Status, "Red") + COUNTIFS(SMEC:SMEC, FIND("Jane Doe", @cell) > 0, Status:Status, "Red")

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Another option would be to create an additional column (SMEall for example) where you join the SME columns and then reference that in a single COUNTIFS.

    .

    SMEall would hold a basic

     

    =JOIN(SMEA@row:SMEC@row, ",")

    .

    Then use

     

    =COUNTIFS(SMEall:SMEall, FIND("Jane Doe", @cell) > 0, Status:Status, "Red")

    .

    Just another option if you run into a similar problem in the future but have more than just a few columns to add together.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!