Summary Sheet Formula

How do I write this formula in on my summary sheet? I'm trying get total count of Reggie's "In Progress" tickets. Will this also count if the field has 2 names in that field? For example the field has both Reggie and Jon.

=COUNTIF([Assigned To]:[Assigned To], "Reggie Andaya", [Status]:[Status], "In Progress")

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Hi @Reggie Andaya

    If you want to use more than one criteria, you should use COUNTIFS instead of COUNTIF. COUNTIF only expects one range and criteria. COUNTIFS will let you have two (or more):

    =COUNTIFS([Assigned To]:[Assigned To], "Reggie Andaya", [Status]:[Status], "In Progress")

    If Assigned To is multiselect you will need to include a HAS function to include the Reggies that are in the same cell as other names. Without HAS the formula is looking for cells that match, so only lone Reggies would be counted. HAS searches for an exact match within the cell.

    =COUNTIFS([Assigned To]:[Assigned To], HAS(@cell, "Reggie Andaya"), Status:Status, "In Progress")

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Hi @Reggie Andaya

    If you want to use more than one criteria, you should use COUNTIFS instead of COUNTIF. COUNTIF only expects one range and criteria. COUNTIFS will let you have two (or more):

    =COUNTIFS([Assigned To]:[Assigned To], "Reggie Andaya", [Status]:[Status], "In Progress")

    If Assigned To is multiselect you will need to include a HAS function to include the Reggies that are in the same cell as other names. Without HAS the formula is looking for cells that match, so only lone Reggies would be counted. HAS searches for an exact match within the cell.

    =COUNTIFS([Assigned To]:[Assigned To], HAS(@cell, "Reggie Andaya"), Status:Status, "In Progress")

  • Reggie Andaya
    Reggie Andaya ✭✭✭✭
    edited 05/01/24

    That worked. What about if I wanted to add more than one status?

    =COUNTIFS([Assigned To]:[Assigned To], "Reggie Andaya", [Status]:[Status], "In Progress", [Status]:[Status], "Not Started")

    Also another formula I'm having trouble with

    =COUNTIF([Area of Request]:[Area of Request],"IT")

    I'm getting #UNPARSEABLE on this one. I'm just wanting a count of all "IT" in that column.

  • KPH
    KPH ✭✭✭✭✭✭
    Question 1

    To add another status and count both, you can include an OR like this:

    =COUNTIFS([Assigned To]:[Assigned To], "Reggie Andaya", [Status]:[Status], OR(@cell = "In Progress", @cell = "Not Started"))

    The way you had it would work like an AND so it would only count the rows where the Status is both In Progress and where it is Not Started which his not possible as it can't be both on the same row.

    Question 2

    This one looks good. Should be parseable:

    =COUNTIF([Area of Request]:[Area of Request],"IT")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!