CountIFS with "OR" "AND"

Hello experts! I'm using these formulas for my dashboard, I have the formula working that will give me the total tasks for "Web/IT" and "IT" as the following:

=COUNTIFS(RESOURCE6:RESOURCE152, OR(@cell = "Web/IT", @cell = "IT")) … the total was 6

Now I want to then calculate out of those total tasks, which are check as Complete. Basically, look at those same two resources, then look in the "Complete" column and give me the total of those that are checked. In the Picture shown I should get "0" but it's just not working for me.

I got this to work with one resource, but I'm having trouble getting it to grab them both. I know I'm missing something simple. Is there an "AND" I'm missing??

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @MatthewVC 

    Hope you are fine, please try the following formula:

    =COUNTIFS(RESOURCE6:RESOURCE152, OR(@cell = "Web/IT", @cell = "IT"),
    COMPLETE6:COMPLETE152, @cell = 1)

    the following screenshot shows the result ( i checked 2 row to show you if you uncheck all it will be 0 )

    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!