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

  • Katie Aldrich
    Katie Aldrich ✭✭
    edited 08/16/21 Answer ✓

    Hi @Bassam Khalil

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


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 08/16/21 Answer ✓

    @Katie Aldrich 

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

    PMP Certified

    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

  • Tim Shaded
    Tim Shaded ✭✭✭✭

    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.

  • Gabby Nepomuceno
    edited 08/13/21

    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.


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    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 )


    PMP Certified

    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"

  • Hi @Bassam Khalil


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

  • Katie Aldrich
    Katie Aldrich ✭✭
    edited 08/16/21

    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.



  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Katie Aldrich

    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.

    PMP Certified

    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"

  • Katie Aldrich
    Katie Aldrich ✭✭
    edited 08/16/21

    Hi @Bassam Khalil

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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Katie Aldrich

    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.

    PMP Certified

    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.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Katie Aldrich 

    I fix it please check it.

    PMP Certified

    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"

  • Katie Aldrich
    Katie Aldrich ✭✭
    edited 08/16/21 Answer ✓

    Hi @Bassam Khalil

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


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 08/16/21 Answer ✓

    @Katie Aldrich 

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

    PMP Certified

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!