Countifs with multiple crtiteria

roya
roya ✭✭✭✭
edited 08/08/24 in Formulas and Functions

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

  • roya
    roya ✭✭✭✭
    edited 08/08/24

    I get the UNPARSABLE error message

  • heyjay
    heyjay ✭✭✭✭✭

    =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",

    ...

  • roya
    roya ✭✭✭✭

    I get an error - #NO MATCH while trying the code above

  • heyjay
    heyjay ✭✭✭✭✭

    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.

    ...

  • roya
    roya ✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!