Trouble with COUNTIFS

JenniferK724

I'm trying to create 2 graphs and am having a heck of a time with the formulas, resulting in UNPARSEABLE. I've successfully built many other formulas, so I'm super stumped as to why I can't get these to work. Here are the two scenarios:

GRAPH 1: Number of Billing Sub-Experiences that are not Complete or Closed

Data Source: sheet named "Master - Experience Improvements"

Criteria 1: Sub-Experience

Sub-Experience Options: Customer Configuration, Customer Billing, Customer Knowledge

Select: Customer Billing

Criteria 2: Status

Status Options: Diagnose, Plan, Execute, Monitor, Complete, Closed

Select: NOT Complete or Closed

Formulas I've tried:

=COUNTIFS({Master - Experience Improvements_Sub-Experience}, "Customer Billing", [{Master - Experience Improvements_Stage}, <> "Complete" OR "Closed"])

=COUNTIFS({Master - Experience Improvements_Sub-Experience}, "Customer Billing", [{Master - Experience Improvements_Stage}], [<> "Complete" OR "Closed"])

=COUNTIFS({Master - Experience Improvements_Sub-Experience}, "Customer Billing", [{Master - Experience Improvements_Stage}, <> "Complete", "Closed"])

I've even tried eliminating the double criteria of Complete and Closed, going just with "not Complete"

=COUNTIFS({Master - Experience Improvements_Sub-Experience}, "Customer Billing", [{Master - Experience Improvements_Stage}], [<>"Complete"]))

=COUNTIFS({Master - Experience Improvements_Sub-Experience}, "Customer Billing", AND [{Master - Experience Improvements_Stage}], [<>"Complete"]))

=COUNTIFS({Master - Experience Improvements_Sub-Experience}, "Customer Billing", AND ({Master - Experience Improvements_Stage}, <>"Complete"))

=COUNTIFS(({Master - Experience Improvements_Sub-Experience}, "Customer Billing"), ({Master - Experience Improvements_Stage}, <>"Complete"))

GRAPH 2: Number of Customer Experiences that are In Progress

Criteria 1: Experience

Experience Options: Customer, Employee, or Partner

Select: Customer

Criteria 2: Health

Health Options: Blocked (red), At Risk (yellow), In Progress (green)

Select: In Progress (green)

My formula: Let's solve for Graph #1 first :)

Can anyone help find the bugs in my formulas?

Genevieve P.

Hi @JenniferK724

We can use the OR Function inside your COUNTIFS to account for the different Status options. Instead of saying OR is not, we'll want to say OR is. This is because "Complete" is not "Closed", and vice versa.

Try this:

=COUNTIFS({Master - Experience Improvements_Sub-Experience}, "Customer Billing", {Master - Experience Improvements_Stage}, OR(@cell = "Diagnose", @cell = "Plan", @cell = "Execute", @cell = "Monitor"))

Then for your second formula, try this:

=COUNTIFS({Experience Column}, "Customer", {Health Column}, "In Progress (green)")

However if you meant that the Health column is a symbol column, you only need to use the word "Green" in quotes, like so:

=COUNTIFS({Experience Column}, "Customer", {Health Column}, "Green")

Cheers,

Genevieve