COUNTIFS parsing error

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

Hi,

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")

Comments

  • 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")

     

    or

     

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

    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