Countif and formula
Hi I am trying to create a formula that counts how many times "Engineering" appears in a column when the column next to it that is either "Delayed, Set Back, At Risk for Delay"
Here is what I tried:
=COUNTIF([Critical Action Department]2:[Critical Action Department]51, CONTAINS("Engineering", @cell)AND([Task Status Based on Due Date]2:[Task Status Based on Due Date]51, CONTAINS("Delayed, At Risk for Delay, Set Back", @cell))
Thank you!
Best Answers
-
I need the formula across the top not in a column, please. Thank you!
This is the correct formula from Bassam:
=IFERROR(COUNT(COLLECT([Critical Action Department]:[Critical Action Department], [Critical Action Department]:[Critical Action Department], CONTAINS(Criteria@row, @cell), [Task Status Based on Due Date]:[Task Status Based on Due Date], OR(CONTAINS("Delayed", @cell), CONTAINS("Set Back", @cell), CONTAINS("At Risk for Delay", @cell)))), "")
-
Done as per your request.
=IFERROR(COUNT(COLLECT([Critical Action Department]:[Critical Action Department], [Critical Action Department]:[Critical Action Department], CONTAINS("Engineering", @cell), [Task Status Based on Due Date *]:[Task Status Based on Due Date *], OR(CONTAINS("Delayed", @cell), CONTAINS("Set Back", @cell), CONTAINS("At Risk for Delay", @cell)))), "")
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Answers
-
Hello Katie,
I was not quite clear on your needed setup, so below are two formulas that should work, depending on which specific setup is needed:
Option 1. If the second column contains the entire text string for "Delayed, At Risk for Delay, Set Back".
=COUNTIFS([Critical Action Department]:[Critical Action Department], CONTAINS("Engineering", @cell), [Task Status Based on Due Date]:[Task Status Based on Due Date], CONTAINS("Delayed, At Risk for Delay, Set Back", @cell))
Option 2. If the second column contains any of the "Delayed", "At Risk for Delay", "Set Back".
=COUNTIFS([Critical Action Department]:[Critical Action Department], CONTAINS("Engineering", @cell), [Task Status Based on Due Date]:[Task Status Based on Due Date], CONTAINS("Delayed", @cell))+COUNTIFS([Critical Action Department]:[Critical Action Department], CONTAINS("Engineering", @cell), [Task Status Based on Due Date]:[Task Status Based on Due Date], CONTAINS("At Risk for Delay", @cell))+COUNTIFS([Critical Action Department]:[Critical Action Department], CONTAINS("Engineering", @cell), [Task Status Based on Due Date]:[Task Status Based on Due Date], CONTAINS("Set Back", @cell))
*Please not that the row numbers are removed, which will review the entire columns, rather than only rows 2 through 51. Hope that won't be an issue. Limiting the rows could pose additional challenge in making the formulas work. Hope this helps.
-
If "Delayed, At Risk for Delay, Set Back" is one criteria, try moving both criteria inside the "And" formula.
Try:
=COUNTIF(AND([Critical Action Department]2:[Critical Action Department]51, CONTAINS("Engineering", @cell), [Task Status Based on Due Date]2:[Task Status Based on Due Date]51, CONTAINS("Delayed, At Risk for Delay, Set Back", @cell))
If those are three different criteria, you might need both "And" and "Or" arguments.
Try:
=COUNTIF(AND([Critical Action Department]2:[Critical Action Department]51, CONTAINS("Engineering", @cell), (OR([Task Status Based on Due Date]2:[Task Status Based on Due Date]51, CONTAINS("Delayed", @cell),[Task Status Based on Due Date]2:[Task Status Based on Due Date]51, CONTAINS("At Risk for Delay", @cell), [Task Status Based on Due Date]2:[Task Status Based on Due Date]51, CONTAINS("Set Back", @cell))
-
Hi Tim and Gabby, neither option worked.
Basically I want a countif formula when Engineering appears count how many times only if the task status based on due date column is not on track (delayed, at risk for delay or set back).
It should return the value of 1.
For Marketing, it should return the value of 5.
-
Hi @Katie Aldrich
Hope you are fine, please try the following formula:
=IFERROR(COUNT(COLLECT([Critical Action Department]:[Critical Action Department], [Critical Action Department]:[Critical Action Department], CONTAINS(Criteria@row, @cell), [Task Status Based on Due Date]:[Task Status Based on Due Date], OR(CONTAINS("Delayed", @cell), CONTAINS("Set Back", @cell), CONTAINS("At Risk for Delay", @cell)))), "")
the following screenshot shows the result ( i create the formula as per criteria )
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
I tried to decipher the formula nd add in the criteria but it still is not working, it still says unparseable.
=IFERROR(COUNT(COLLECT([Critical Action Department]1:[Critical Action Department]60,
[Critical Action Department]1:[Critical Action Department]60, CONTAINS("Engineering" @row, @cell),
[Task Status Based on Due Date]1:[Task Status Based on Due Date]60,
OR(CONTAINS("Delayed", @cell), CONTAINS("Set Back", @cell),
CONTAINS("At Risk for Delay", @cell)))))
-
HI @Bassam Khalil @Tim Shaded @Gabby Nepomuceno
I have also tried this formula seems the closest when I type it in Smartsheet:
OPTION 1:
=COUNTIF([Critical Action Department]1:[Critical Action Department]50, CONTAINS("Engineering", @cell)), AND(COUNTIF([Task Status Based on Due Date *]1:[Task Status Based on Due Date *]50, OR(CONTAINS("Delayed", @cell), CONTAINS("Set Back", @cell), CONTAINS("At Risk for Delay", @cell))))
OPTION 2:
=COUNTIF([Critical Action Department]1:[Critical Action Department]50, CONTAINS("Engineering", @cell)), AND(COUNTIF([Task Status Based on Due Date *]1:[Task Status Based on Due Date *]50, CONTAINS("Delayed", @cell), OR(CONTAINS("Set Back", @cell)), OR(CONTAINS("At Risk for Delay", @cell))))
These formulas work separately but not together.
=COUNTIF([Critical Action Department]1:[Critical Action Department]50, CONTAINS("Engineering", @cell)),
=COUNTIF([Task Status Based on Due Date *]1:[Task Status Based on Due Date *]50, OR(CONTAINS("Delayed", @cell), CONTAINS("Set Back", @cell), CONTAINS("At Risk for Delay", @cell)))
It should return the value of 1.
-
It's working in my sample sheet, please share me as an admin on a copy of your sheet after removing any sensitive data and I will try to fix it for you.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
There is too much information for me to share this sheet with you. The formula you provided doesn't have any row ranges, is this why it isn't working?
The formulas I have work separately but they don't work combined. How do I combine them to return the value of 1 for Engineering like my example?
=COUNTIF([Critical Action Department]1:[Critical Action Department]50, CONTAINS("Engineering", @cell)),
=COUNTIF([Task Status Based on Due Date *]1:[Task Status Based on Due Date *]50, OR(CONTAINS("Delayed", @cell), CONTAINS("Set Back", @cell), CONTAINS("At Risk for Delay", @cell)))
-
The range in My formula contain the whole column, I don't want you to share the original sheet.
Save a copy of your sheet and delete all sensitive data in this copy and keep a sample data only to test the formula then share me as an admin on this copy to check what is the problem.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
@Bassam Khalil I have shared a sample sheet.
-
Hi @Katie Aldrich
I fix it please check it.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
I need the formula across the top not in a column, please. Thank you!
This is the correct formula from Bassam:
=IFERROR(COUNT(COLLECT([Critical Action Department]:[Critical Action Department], [Critical Action Department]:[Critical Action Department], CONTAINS(Criteria@row, @cell), [Task Status Based on Due Date]:[Task Status Based on Due Date], OR(CONTAINS("Delayed", @cell), CONTAINS("Set Back", @cell), CONTAINS("At Risk for Delay", @cell)))), "")
-
Done as per your request.
=IFERROR(COUNT(COLLECT([Critical Action Department]:[Critical Action Department], [Critical Action Department]:[Critical Action Department], CONTAINS("Engineering", @cell), [Task Status Based on Due Date *]:[Task Status Based on Due Date *], OR(CONTAINS("Delayed", @cell), CONTAINS("Set Back", @cell), CONTAINS("At Risk for Delay", @cell)))), "")
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!