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.



  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 08/04/21

    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")

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!