COUNTIFS with ANDs and ORs

John Jonassen

Hey All, formula help

I am looking to match the logic used in the filters of a report, but the AND and OR or the formula writing is really crossing my eyes.

My criteria is as follows, its the 3 ORs at the end that is my challenge.

{Tasked User} = [User Name]@row

AND {Completion Date} ISBLANK

AND

• {Submit Task Package} is NOT "Not Assigned", "On Hold", "Accepted"
• OR {Rework} = "Green"
• OR {Status} is "With Invoicing" or "With Tech"

Any help would be greatly appreciated!

DKazatsky2

Hi @John Jonassen,

One way to do this is to use a "helper" column in the base sheet to handle the OR part of the formula. This can be checkbox with the following formula.

=IF(OR(AND([Submit Task Package]@row <> "Not Assigned", [Submit Task Package]@row <> "On Hold", [Submit Task Package]@row <> "Accepted"), Rework@row = "Green", [Status]@row = "With Invoicing", [Status]@row = "With Tech"), 1)

Then, on the sheet where you are using the COUNTIFS, this formula should work.

=COUNTIFS({Tasked User}, =[User Name]@row, {Completion Date}, ISBLANK(@cell ), {Helper}, =1)

The helper column can be hidden if needed.

Hope this helps,

Dave