COUNTIFS Using And as well as Or
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
Comments
-
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.
For more information on working with the COUNTIFS function please see this article from our Help Center: https://help.smartsheet.com/function/countifs
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.
Please let me know if you have any questions about this and I’ll be more than happy to advise further!
-
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 = ""))
-
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"))
-
-
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!
Best,
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | 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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!