COUNTIFS Using And as well as Or

Andrée Starå

Hi @shuckel

I hope you're well and safe!

Try something like this.

=COUNTIFS(ColumnA:ColumnA, "identify", ColumnB:ColumnB, "identify")

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 or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

mbsamuel6

Hi team, I'm bumping this thread since I need some help.

Screenshot below for reference.

I am trying to write a formula using countifs and the OR statement to basically say: count the number of active projects that has an "affordable" Breakthrough Objective.

I tried following the thread and the best formula I came up with was:

=COUNTIFS({Product Prioritization Board Range 3}, "Affordable", {Copy of Product Prioritization Board Range 2}, "Active (Foundational)", OR(@cell = " Define", @cell = "Evaluate"))

"Product Prioritization Board" is the sheet shown above. I am referencing that sheet from a different sheet were all my metrics are stored.

The way I'm reading the formula above is to say count all active projects that is listed "Active (Foundational), Define or Evaluate that has a Breakthrough Objective of "Affordable"

I'm getting "invalid operation"

Note: "Breakthrough" objective column does have multi select

Tagging @Andrée Starå or @Paul Newcome

mbsamuel6

Good news I figured it out. My OR Statement was used wrong. The OR should be written for the criteria in the IF Statement. Looks like I was writing the OR statement after I already made a criteria.

New formula is

=COUNTIFS({Product Prioritization Board Range 3}, "Affordable", {Copy of Product Prioritization Board Range 2}, OR(@cell = "Evaluate", @cell = "Active (Foundational)", @cell = "Define")

Michael Chohrach

I'm trying to create a formula that will count if either "Pole Change Out" column or "Misc." column are not blank. I'm thinking I need to use the OR function in combination with COUNTIF (or COUNTIFS) but nothing I do seems to work. Appreciate any suggestions.

=COUNTIFS(OR([Pole Change Out]:[Pole Change Out] <> "", ([Misc.]:[Misc.] <> "")))

Nate H

I'm hoping someone can help me figure out why I'm getting a #invalid operation error with this formula. I'm trying to use it to count the number of Harvey balls at 3/4 or full across a range of columns where there is Enablement in row 67:

=COUNTIFS(ACP$67:Vicinia$67, "Enablement", OR(ACP@row:Vicinia@row = "Three Quarter", ACP@row:Vicinia@row = "Full"))

Paul Newcome

@Nate H Are you able to provide a screenshot for context?

Nate H

@Paul Newcome I've attached the screenshot. I wasn't able to stretch out to the Vicinia column, but just imagine that last header has Vicinia in it.

Paul Newcome

@Nate H I want to make sure I understand correctly...

You want to count "@row" (e.g. on the second row from the top in your screenshot) those cells that are either Three Quarter or Full, but only in the columns where "Enablement" is listed there on the bottom row?

Nate H

@Paul Newcome You are correct.

Paul Newcome

@Nate H Give this a try:

=COUNTIFS(ACP$67:Vicinia$67, @cell = "Enablement", ACP@row:Vicinia@row, OR(@cell = "Three Quarter", @cell = "Full"))

Nate H

Worked like a charm! Brilliant once again, @Paul Newcome. Thank you. I wouldn't have figured that out on my own.

Paul Newcome

@Nate H Happy to help. 👍️

Pat Rio

Hi! Tried doing a COUNTIFS with OR but I got a "Invalid Data Type" error. Here's my formula:

=COUNTIFS({assetTier}, Tier@row, {assetCompany}, "APMC", OR({assetRemarks}, "With ISG - Resigned Employee", {assetRemarks}, "With ISG - Service Unit", {assetRemarks}, "With ISG - For Repair", {assetRemarks}, "With ISG - Mantra", {assetRemarks}, "With ISG - Floating Unit"))

Hope someone can help me find the issue. Thank you!

Paul Newcome

@Pat Rio You would use "@cell" how it is in the previous examples. You do not swap it out for a {Cross Sheet Reference}.

Pat Rio

@Paul Newcome thanks for the response! I just did a workaround tho and had my formula look like this:

=COUNTIFS({assetTier}, Tier@row, {assetCompany}, "APMC", {assetRemarks}, "With ISG - Resigned Employee") + COUNTIFS({assetTier}, Tier@row, {assetCompany}, "APMC", {assetRemarks}, "With ISG - Service Unit") + COUNTIFS({assetTier}, Tier@row, {assetCompany}, "APMC", {assetRemarks}, "With ISG - For Repair") + COUNTIFS({assetTier}, Tier@row, {assetCompany}, "APMC", {assetRemarks}, "With ISG - Mantra") + COUNTIFS({assetTier}, Tier@row, {assetCompany}, "APMC", {assetRemarks}, "With ISG - Floating Unit")