COUNTIFS parsing error

Peggy

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

Nic Larsen

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

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