Countifs with multiple crtiteria
Hello,
I have been going at this for a while and not able to see what I may be doing wrong.
I am trying to count all EDAP Data Support issues which was created after 01/01/2023, which are still open ( not in Done or cancelled status)
Here is the formula Im using .
There is something wrong with the "CONTAINS(Category@row, @cell), "EDAP Support Issues"
The formula works without it
My formula
=COUNTIFS([Creation Date]:[Creation Date], @cell >= DATE(2023, 1, 1), CONTAINS(Category@row, @cell), "EDAP Support Issues", (Status:Status, NOT(OR(CONTAINS("Done", @cell), CONTAINS("Canceled", @cell))))
Note : The Categry field is a computed column. I was not sure if the summary metrics ( COUNTIFS) can be based of a computed column
Will really appreciate if anyone can help me
Answers
-
I get the UNPARSABLE error message
-
=COUNTIFS( Category:Category, "EDAP Support Issues", [Creation Date]:[Creation Date], >=DATE(2023, 1, 1), Status:Status, <>"Done", Status:Status, <>"Cancelled")
Add more status criteria as needed,
Status:Status, <>"Others",
...
-
I get an error - #NO MATCH while trying the code above
-
Not sure how you ended up with #NO Match. Can you please check if you have the columns are matching the formula. I might have some type or our columns does not match. Your Creation date column should be set as DATE in the properties.
Please also share a snip of your sheet so we can further investigate.The formula is working OK on my end.
...
-
Thanks so much for your help and support.
The issue is that the CATEGORY field is populated from a formula driven from an EPIC formula (see below)
Then I was trying to count the instances of Category in the SHEET SUMMARY using the COUNTIFS suggested by you. This formula works when If use a new sheet (with no formulas on the Category) , but doesnt work in my existing sheet
=COUNTIFS( Category:Category, "EDAP Support Issues", [Creation Date]:[Creation Date], >=DATE(2023, 1, 1), Status:Status, <>"Done", Status:Status, <>"Cancelled")
Category Formula
=IF(CONTAINS("Bugs", [Epic Name]@row), "Bugs", IF(CONTAINS("Project", [Epic Name]@row), "Project| New Features", IF(CONTAINS("TechDebt", [Epic Name]@row), "DataOps TechDebt", IF(CONTAINS("A360", [Epic Name]@row), "A360 Request", IF(CONTAINS("Support", [Epic Name]@row), "EDAP Support Issues", IF(CONTAINS("Optimization", [Epic Name]@row), "Data Optimization", IF(CONTAINS("Regression", [Epic Name]@row), "Regression Testing", " ")))))))
EPIC Formula
=INDEX({DataOps Epics Range 1}, MATCH(Parent@row, {DataOps Epics Range Issue Key}, 0))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 143 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!