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

Find more posts tagged with

Formulas

Comments

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

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!

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

Jenn Pestalozzi

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

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

Jenn Pestalozzi

Amazing! That worked great! Thanks Paul :)

Paul Newcome

Happy to help! 👍️

Ricky Quiroz

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

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

Ricky Quiroz

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

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.

Angela Cisneros

@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

@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

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

Joe Haney

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!

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Counting with multiple criteria
I dont know what im doing wrong here, im trying to count anything with Mentoring and if Q1 is found =COUNTIFS({RelatedGoalWoW}, HAS(@cell , "Mentoring")) + COUNTIFS({Quarter}, HAS(@cell , "Q1"))
Help with addition to formula
Hi, i need to make an addition to the below formula please. I need to add if column type is issue and impact score is 4 - gray, 3 - red, 2, amber and 1 green =IF(AND(Type@row = "Risk", ([Impact Score]@row * [Likelihood Score]@row ) >= 20), "Gray", IF(AND(Type@row = "Risk", ([Impact Score]@row * [Likelihood Score]@row ) >=…
How can I use Conditional Formatting for date in the past or future when a formula created the date
Hi Everyone, Thank you for your time in reading this. How can I use Conditional Formatting for date in the past or future when a formula created the date? The black columns are different dates generated. The 2 yellow columns are then selecting the relevant date based on different criteria. The white columns are me trying…