COUNTIFS with AND/OR

Graham Rimell

I'm trying to get a COUNTIFS where it's got an OR of two different sheet references.

Its the OR that's causing me issues. I can get an OR @CELL formula that will work for different values in a single column, but not two different columns.

The formula is essentially: =COUNTIFS({Approval State07}, "closed", {Status07}, "Complete" OR({Source07} = "Improve", {Category07} = "Improve"). This doesn't work, but it's the basic argument that I'm attempting.

So it counts how many rows have (Approval State = Closed) AND (Status = Complete) and then if either (Source = Improve) OR (Category = Improve).

The "improve" tag can appear in either one of those columns, or both.

I've heard COLLECT might be a useful tool to do this, but I've yet to use it much so not sure how it works.

Thanks.

Bassam Khalil

Hi @Graham Rimell

Hope you are fine, please try the following formula:

=COUNTIFS({Approval State07}, @cell = "closed", {Status07}, @cell = "Complete", 
{Source07}, @cell = "Improve")+COUNTIFS({Approval State07}, @cell = "closed", {Status07}, 
@cell = "Complete",{Category07}, @cell = "Improve")