using COUNTIFS with contains

deb_63_hydracor

Hello,

I'm trying to count the number of rows where deployment complete or cancelled is not checked, where Deployment Type is "New Deployment" and Deployment Tier is either "Plus" or "Plus Unlimited." The formula I'm using is getting an Invalid Operation error.

=COUNTIFS({Deployment Complete}, "0", {Deployment Queue Project Cancelled}, "0", {Deployment Type}, "New Deployment", {Deployment Tier}, CONTAINS("Plus"))

Thanks for your help!

Andrée Starå

Hi @deb_63_hydracor

I hope you're well and safe!

At a glance, I see two errors (surrounding numbers with "").

Try something like this.

=
COUNTIFS({Deployment Complete}, 0, 
{Deployment Queue Project Cancelled}, 0, 
{Deployment Type}, "New Deployment", {Deployment Tier}, CONTAINS("Plus"))

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

deb_63_hydracor

Thanks for such a quick response!

I'm still getting an Invalid Operation error using this formula:

=COUNTIFS({Deployment Complete}, 0, {Deployment Queue Project Cancelled}, 0, {Deployment Type}, "New Deployment", {Deployment Tier}, CONTAINS("Plus"))

Leibel S

@deb_63_hydracor

See below proper syntax for using the CONTAINS function in such a situation.

=COUNTIFS({Deployment Complete}, 0, {Deployment Queue Project Cancelled}, 0, {Deployment Type}, "New Deployment", {Deployment Tier}, CONTAINS("Plus", @cell))

Andrée Starå

@Leibel Shuchat

Nice catch! 👍️

deb_63_hydracor

Thank you, that worked! Have a great week!

deb_63_hydracor

Hello again,

The above formula worked perfectly everywhere on my dashboard except for one. Here, I'm trying to count all rows with sales type of Package Upgrade, Sales Tier contains Plus, Sales & Leads box is checked, Sales Lead Complete box is unchecked and Sales Status does not equal.... The formula worked before I added the CONTAINS portion.

=COUNTIFS({Sales Type}, "Package Upgrade", {Sales Tier},CONTAINS("Plus",@cell), {Sales & Leads}, "1", {Sales Lead Complete}, 0, {Sales Status}, <>"Prospect", {Sales Status}, <>"Hold for Clarification", {Sales Status}, <>"Hold for Future Follow-up"))

Thank you!

Leibel S

@deb_63_hydracor

Your formula may have worked previously but does not match your description:.

If you want to count those where Sales & Leads box is checked, the syntax should be {Sales & Leads}, 1 (no quote marks around the 1)

Nyasha

What is wrong with this formula - I'm getting an "Incorrect argument Set" error message:

=COUNTIFS({CRP-Tracking-Sheet Project}, [C1]40, {CRP-Tracking-Sheet Type}, "E2E", {CRP-Tracking-Sheet Level}, >0, CONTAINS("COM", {CRP-Tracking Sheet UseCaseOwner}))

Thanks!

Leibel S

@Nyasha

Try the below:

=COUNTIFS({CRP-Tracking-Sheet Project}, @cell=[C1]40, {CRP-Tracking-Sheet Type}, @cell="E2E", {CRP-Tracking-Sheet Level}, @cell>0, {CRP-Tracking Sheet UseCaseOwner},CONTAINS("COM", @cell))

Nyasha

Thanks Leibel.

Kind of confusing because the CONTAINS syntax seems reversed😎

Thanks all the same!