# COUNTIFS Using And as well as Or

edited 12/09/19

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.

Sincerely,

Diego

Tags:
«13

• ✭✭✭✭✭✭

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

• ✭✭✭✭

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.

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 (https://help.smartsheet.com/articles/2476491#cell). More information on the OR function can also be found here if needed (https://help.smartsheet.com/function/or). 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.

• 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!

Jenn

• ✭✭✭✭✭✭

@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 :)

• ✭✭✭✭✭✭

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?

• ✭✭✭✭✭✭

@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.

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

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

• edited 06/11/20
• ✭✭✭✭✭✭

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!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | 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!