COUNTIFS parsing error

Peggy · 01/18/19

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 · 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 · 01/22/19

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

COUNTIFS parsing error

Comments

Categories