CountIFS with "OR" "AND"

MatthewVC

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??

Bassam Khalil

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 )

Bassam Khalil

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 )

MatthewVC

https://community.smartsheet.com/discussion/comment/307556#Comment_307556

OH man Bassam, THANK YOU!!! this worked perfectly!!!!!

Bassam Khalil

@MatthewVC

Excellent, i will be happy to help you any time.and I will be grateful for your "Vote Up" or "Insightful"