COUNTIFS parsing error

Peggy ✭✭✭✭
edited 12/09/19 in Smartsheet Basics


I reviewed the other countifs suggestions, but i am still getting a parsing error. 


I am trying to count all rows that have EBS in range 1 where range 2 has only completed and in process.

=COUNTIFS({2018 TRACKER Range 1}, "EBS", {2018 TRACKER Range 2}, "Completed", {2018 TRACKER Range 2} "In Progress")


  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    edited 01/18/19

    Your formula is missing a comma before "In Progress". 

    Are you trying to count every time you have EBS in one column and Completed or In Progress in another column? 

    Edit: Try this but with your column references.

    =COUNTIFS([2018 Tracker]1:[2018 Tracker]5, "EBS", [Status Left]1:[Status Left]5, "Completed") + COUNTIFS([2018 Tracker]1:[2018 Tracker]5, "EBS", [Status Left]1:[Status Left]5, "In Progress")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Whenever you put multiple criteria in a COUNTIFS or SUMIFS, the formula is looking for instances where ALL criteria is true, so unless you have a status that says both "completed" and "in progress" at the same time, your current formula will always return a 0. Try something like this instead when you are looking at the same range, but the criteria is this or that...


    =COUNTIFS({2018 TRACKER Range 1}, "EBS", {2018 TRACKER Range 2}, OR(@cell = "Completed", @cell = "In Progress"))


    Another option would be to use a COUNTIFS and exclude what you don't want (may be easier or harder depending on how many different status possibilities there are). 


    =COUNTIFS({2018 TRACKER Range 1}, "EBS", {2018 TRACKER Range 2}, @cell <> "Not Started")




    =COUNTIFS({2018 TRACKER Range 1}, "EBS", {2018 TRACKER Range 2}, NOT(@cell = "Not Started"))