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

Status Log Push to Dashboard
I want to push the latest status log sheet entry to the dashboard. I have added working columns for the last date, recent value (0 or 1) and recent status (text field). The recent columns are blank except for the most recent data rows (that part of the sheet is working). I have a project metric sheet template across topics…
Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
How to Create Time Duration
I have a report where I need a formula to figure out time duration. For example, employee starts at 8:00 am and ends at 9:00 am, I need the next column to tell me 1.00 to indicate he worked 1 hour. I will be using military time throughout this sheet.