Trying to get a count of status for specific team members


I am trying to find out how many initiatives with a specific status have certain team members in the Project Team column. For example, how many initiatives is Leo on the team where the status is Not Started? I have tried the following:

=COUNTIFS({EITPort_Status_24}, "Not Started", FIND("Leo Liao", @cell, >0)) - Gives me an Invalid Operation error

=COUNTIFS({EITPort_Status_24}, "Not Started", HAS(@cell, "Leo Liao")) - Gives me an Incorrect Argument error

Any thoughts on how to make this work?


  • Mark.poole
    Mark.poole ✭✭✭✭✭✭

    @Alisa Buck How large is the reference you are using is it covering all the columns required?

    Also. COUNTIFS() function works like this.

    =COUNTIFS(Range,Criteria, Range, Criteria,Range, Criteria)

    you have to give a range to look before every criteria to pull the information.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You are close with both.

    =COUNTIFS({EITPort_Status_24}, "Not Started", {Source Sheet Contact Column}, FIND("Leo Liao", @cell) >0)

    =COUNTIFS({EITPort_Status_24}, "Not Started", {Source Sheet Contact Column}, HAS(@cell, "Leo Liao"))

  • Alisa Buck
    Alisa Buck ✭✭✭

    Thanks all! I got it to work using…

    =COUNTIFS({EITPort_Team_24}, FIND("Leo Liao", @cell) > 0, {EITPort_Status_24}, FIND("Not Started", @cell) > 0)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!