Formula for countif and contains

I am having trouble creating formula that adds two fields. The first field must contain the word strategy, and the second field can be one of several status. Here is one attempt of the formula:

=COUNTIFS([Project Type]:[Project Type],CONTAINS("Strategy", @cell), AND(COUNTIFS([Project Phase]:[Project Phase], “Execution”, [Project Phase]:[Project Phase], “Planning”, [Project Phase]:[Project Phase], “Initiation”, [Project Phase]:[Project Phase], “Vendor Selection”, [Project Phase]:[Project Phase], “Monitoring/Controlling”, [Project Phase]:[Project Phase], “Combined into another Project”, [Project Phase]:[Project Phase], “On-Hold”))))


Not sure where this failing

Best Answers

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    Answer ✓

    I found that we got a typing error in the formula as below:


    Retry this formula:

    =COUNTIFS([Project Type]:[Project Type], CONTAINS("Strategy", @cell)) + COUNTIFS([Project Phase]:[Project Phase], OR(@cell = "Planning", @cell = "Execution", @cell = "Initiation", @cell = "Vendor Selection", @cell = "Monitoring/Controlling", @cell = "Combined into another Project"))


    Gia Thinh Co. - Smartsheet Solution Partner.

  • Jaz693
    Jaz693 ✭✭✭✭
    Answer ✓

    That worked! Thank you so much!!

Answers

  • Jaz693
    Jaz693 ✭✭✭✭

    Unfortunately no> I went to that one and tried several other variations:


    =COUNTIF(CONTAINS(([Project Type]:[Project Type]), "Strategy")),COUNTIFS([Project Phase]:[Project Phase], "Execution") + COUNTIFS([Project Phase]:[Project Phase], "Initiation") + COUNTIFS([Project Phase]:[Project Phase], "Vendor Selection") + COUNTIFS([Project Phase]:[Project Phase], "Planning") + COUNTIFS([Project Phase]:[Project Phase], "Monitoring/Controlling") + COUNTIFS([Project Phase]:[Project Phase], "Combined into another Project")

    OR

    =COUNTIFS((([Project Type]:[Project Type], “RCU Strategy”)+ COUNTIF(([Project Type]:[Project Type], “RCUSG Strategy”))), [Project Phase]:[Project Phase], OR(@cell = "Planning", @cell = "Execution", @cell = "Initiation", @cell = “Vendor Selection”, @cell = “Monitoring/Controlling”, @cell = “Combined into another Project”))


    I'm stumped. Do I have too many parenthesis? not enough arguments? The error I receive is Unparseable regardless of the changes I make

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭

    Hi

    In case you want to add 2 fields, I think we may split your original formula as below:

    =COUNTIFS([Project Type]:[Project Type],CONTAINS("Strategy", @cell) + COUNTIFS([Project Phase]:[Project Phase], OR(@cell = "Planning", @cell = "Execution", @cell = "Initiation", @cell = “Vendor Selection”, @cell = “Monitoring/Controlling”, @cell = “Combined into another Project”))


    Gia Thinh Co. - Smartsheet Solution Partner.

  • Jaz693
    Jaz693 ✭✭✭✭

    Getting closer, Im still getting unparseable with the above. Once I start to enter the Project Phase variables the color coding of the field names go away, almost like they arent recognizable anymore????

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭

    Can you show some screenshots to demonstrate the situation?


    Gia Thinh Co. - Smartsheet Solution Partner.

  • Jaz693
    Jaz693 ✭✭✭✭

    I think this should give you an idea

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    Answer ✓

    I found that we got a typing error in the formula as below:


    Retry this formula:

    =COUNTIFS([Project Type]:[Project Type], CONTAINS("Strategy", @cell)) + COUNTIFS([Project Phase]:[Project Phase], OR(@cell = "Planning", @cell = "Execution", @cell = "Initiation", @cell = "Vendor Selection", @cell = "Monitoring/Controlling", @cell = "Combined into another Project"))


    Gia Thinh Co. - Smartsheet Solution Partner.

  • Jaz693
    Jaz693 ✭✭✭✭
    Answer ✓

    That worked! Thank you so much!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!