COUNTIFS Using And as well as Or

diegoartorres ✭✭
edited 12/09/19 in Formulas and Functions

Hello everyone,

I am looking to have an information sheet that is looking for several criteria with "and" and "or".

For example, I want the formula to count if the other sheet has "Priority 1" and "John Doe" and a status of either 1 or 2 or 3.

I will have another cell on the information sheet for "Priority 2" and "John Doe" and a status of either 1 or 2 or 3.

I am not using children and the sheet I am referencing is one that I am shared with as Editor - cannot share, in case that is an issue.

Thank you.





  • L_123
    L_123 ✭✭✭✭✭✭

    I recommend looking into the formula COLLECT. 


    Collect returns an array based on criteria. That is you give a range of values you want to return, and any number of ranges that you want to filter based on criteria. Collect returns an array in the format {A B C D}.

    You use this as a range for another formula. you could use count, index, sum, join, or many other formulas to analyze the return from collect.  In your case it would be something like

    =count(collect([other sheet reference priority column],[other sheet reference priority column],@cell = Priority 1,[other sheet reference status column],or(@cell =1, @cell =2, @cell =3))

  • Kelsey Hayden
    Kelsey Hayden ✭✭✭✭

    Hello Diego,

    Thanks for reaching out! It sounds like you are trying to create a formula to count the number of rows that contain "Priority 1", "John Doe" and a status of either 1, 2 or 3.

    To do this I recommend using the COUNTIFS function, which counts the number of cells within a range that meet all of the specified criteria.

    For more information on working with the COUNTIFS function please see this article from our Help Center:

    The formula would also use the OR function along with @cell. If you're not familiar with @cell, check out this Help Center article for more information ( More information on the OR function can also be found here if needed ( Here's an example of how this formula could look:

    =COUNTIFS({Sheet Range 1}, "Priority 1", {Sheet Range 2}, "John Doe", {Sheet Range 3}, OR(@cell = 1, @cell = 2, @cell = 3))

    To make the formula more efficient, I recommend inserting the Priority numbers into your information sheet and including the cell references as the criterion in the formula. Here is how the formula would look:

    =COUNTIFS({Sheet Range 1}, Priority1, {Sheet Range 2}, "John Doe", {Sheet Range 3}, OR(@cell = 1, @cell = 2, @cell = 3))

    Please see the attached screenshot to see what this formula would look like in a sheet. 

    Please let me know if you have any questions about this and I’ll be more than happy to advise further!

    Screen Shot 2018-10-23 at 9.06.13 PM.png

  • Hi Kelsey,

    I wonder if you could help me. I am trying to build a formula to count the following:

    A project Assigned to Akil with any of the following statuses: New/Not Started, In Progress, and In Progress - Scraping.

    I was able to build a formula for all of Akil's completed projects, but it gets hairy when I try to add more statuses to my formula.

    Worked: =COUNTIFS([Assigned To]:[Assigned To], "Akil", Status:Status, ("Complete"))

    Would like to achieve: =COUNTIFS([Assigned To]:[Assigned To], "Akil", Status:Status, ("STATUSES LISTED HERE"))

    Any suggestions would be awesome!


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Jenn Pestalozzi Give something like this a try...

    =COUNTIFS([Assigned To]:[Assigned To], "Akil", Status:Status, OR(@cell = "New/Not Started", @cell = "In Progress", @cell = "In Progress - Scraping"))

  • Amazing! That worked great! Thanks Paul :)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! 👍️

  • I'm having an issue referencing another sheet. I'm trying to do a COUNTIF formula to add up for columns "Position Status" are set to filled and "Faculty/CA?" column is set to yes. Should I be using a CountIF with AND?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Ricky Quiroz You should use a COUNTIFS (with the "S" on the end) to allow for multiple range/criteria sets (link included).

    =COUNTIFS({Other Sheet Position Status Column}, "filled", {Other Sheet Faculty/CA? Column}, "Yes")

  • @Paul Newcome You are the man! Thank you so much. That "S" was the missing link.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Please don't forget to mark the most appropriate response as "helpful" so that others searching for a similar solution can know that one may be found.

  • @Paul Newcome great tips! I am trying to use the OR function when referencing another sheet. Do you know how that should be written? I want it to count when the End Date is in the past or is blank. Here is what I currently have that is giving me the Invalid data time error:

    '=(COUNTIFS({Project Plan Complete}, <1, {Project Plan Responsible}, FIND("Logistics", @cell) > 0, OR({Project Plan - End Date}, <TODAY(),{Project Plan - End Date}, ""))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Angela Cisneros You would need to use @cell references.

    =COUNTIFS({Project Plan Complete}, < 1, {Project Plan Responsible}, FIND("Logistics", @cell) > 0, {Project Plan - End Date}, OR(@cell < TODAY(),@cell = ""))

  • Joe Haney
    Joe Haney
    edited 06/11/20

    Hi Paul - I am trying to use COUNTIFS to count the number of cells in a range on one sheet that has either "In Progress" or "Completed" to update RYG status. Any suggestions?

    Here is the formula I am trying to use that is giving me the #unparseable error:

    =IF(((COUNTIFS([Study Setup]2:[Third Party Labs]2, "In Progress")) + (COUNTIFS([Study Setup]2:[Third Party Labs]2, "Completed"))) >= 3, "Green", IF(((COUNTIFS([Study Setup]2:[Third Party Labs]2, "In Progress")) + (COUNTIFS([Study Setup]2:[Third Party Labs]2, "Completed"))) = 2, “Yellow”, "Red"))

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Joe Haney

    At a quick glance you have one too many parenthesis in many of the parts.

    I hope that helps!

    Be safe and have a fantastic week!


    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!