Countifs with Multiple Criteria

Options
rgallego18
edited 12/09/19 in Smartsheet Basics

Hello Smartsheet Community, 

I need another help from you. I am having a hard doing the countifs with multiple criteria.

I need to count the number of "In Progress" project per Project Lead but I it makes it complicated because there are some cells that have 2 names in it.

This is the formula I currently have in Smartsheet, to get the total number of projects that are in Progress under under Person A but it only counts the cell with a single name.

=COUNTIFS({RANGE 1}, "Person A", {RANGE 2}, "In Progress")

 

=COUNTIFS({RANGE 1}, "*Person A*", {RANGE 2}, "In Progress") 

-> this second formula, is an excel formula which counts all the entry for "Person A". I added asterisk (*) and it worked. This is exactly what I need but it everytime I do this I keep getting "0" value.

 

Thanks again for all the help :)

2019-01-28_1345.png

Comments

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

    Hi,

    Try this.

    I've added a Helper Column called Names to put the name you're searching for, but you could change it in the formula instead if you like.

    Change the ranges to the cross-sheet ranges you'd like to use.

    =COUNTIFS(Status:Status; "In Progress"; [Project Lead]:[Project Lead]; FIND(Names@row; @cell) > 0)    

    The same version but with the below changes for your and others convenience.    -=COUNTIFS(Status:Status, "In Progress", [Project Lead]:[Project Lead], FIND(Names@row, @cell) > 0)

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

    Would that work?

    Have a fantastic week!

    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.

  • rgallego18
    Options

    Hi Andree, 

    Thanks for the help! Tried it but it is not working or I'm doing it wrong. :(

    Question.

    When you say...

    Status:Status - is this the 1st RANGE?

    [Project Lead]:[Project Lead] - the 2nd RANGE?

    I'm also lost on this part: FIND(Names@row, @cell) > 0)

    Is the Names@row another range like RANGE 3?

    I entered it this way and I keep getting an this message -> #UNPARSEABLE

     

    =COUNTIFS({Range 1}, "In Progress", {Range 2}, FIND(Person A), {Range 3}>0))

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 01/28/19
    Options

    Happy to help!

    I've added my answers below.

    Question.

    When you say...

    Status:Status - is this the 1st RANGE?

    I've changed the order, so this is the Range for the "In Progress"

    [Project Lead]:[Project Lead] - the 2nd RANGE?

    I've changed the order, so this is the Range for "Person A, Person B" and so on.

    I'm also lost on this part: FIND(

    Names@row, @cell) > 0)

    Is the Names@row another range like RANGE 3?

    This is a list of the names in a list on the same sheet, but you could change it in the formula instead.

    I entered it this way and I keep getting an this message -> #UNPARSEABLE

    =COUNTIFS({Range 1}, "In Progress", {Range 2}, FIND(Person A), {Range 3}>0))

    Try this.

    =COUNTIFS({Range for status}, "In Progress", {Range for Project Lead}, FIND("Person A", @cell) > 0)

    Did it work?

    Best,

    Andrée

     

    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.

  • rgallego18
    Options

    Hey Andree!

    You're such a Smartsheet Wizard!!!

    It worked, yay!!!

    THANK YOU SO MUCH!!!

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

    I'm always happy to help!

    Smartsheet Wizard! That's a new one! Thanks! laugh

    Happy to hear that you got it working!

    Best,

    Andrée

    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.

  • Jeff Muschett
    Options

    Hi @Andrée Starå , I'm having a similar issue. Your formula above, =COUNTIFS({Range for status}, "In Progress", {Range for Project Lead}, FIND("Person A", @cell) > 0) worked for me, however there is one snag.


    In my second range (Range for Project Lead in the above formula) I have two columns selected. Let's call them Project Lead and Project Manager. When I add both columns to that tracker range, I get an #INCORRECT ARGUMENT flag.

    The request I have is "I want to know how many in progress projects person A is either a Project Lead or Manager on."


    Thanks!

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

    Hi @Jeff Muschett,

    What’s your formula?

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

    I hope that helps!

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    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.