Using COUNTIFS and two CONTAINS

I've read most of the answers and using the recommendations, I'm still getting the error #UNPARSEABLE

This FORMULA works, but I need to add another search for "DC"

=COUNTIFS([DC - Status]:[DC - Status], "Incorrect DC") + COUNTIFS([DC - Note]:[DC - Note], (CONTAINS("duplicate", @cell)))

so I've add another COUNTIFS and CONTAINS but it's giving me an error. Also, tried variations and neither works. This FORMULA is the one that returns #UNPARSEABLE:

=COUNTIFS([DC - Status]:[DC - Status], "Incorrect DC") + COUNTIFS([DC - Note]:[DC - Note], (CONTAINS("duplicate", @cell))), + COUNTIFS([DC - Note]:[DC - Note], (CONTAINS("dc", @cell)))

Any help would be greatly appreciated.

Thanks so much!

Best Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓

    Looks like a syntax error. You have an unnecessary comma.

    Try the corrected formula.

    = COUNTIFS([DC - Status]:[DC - Status], "Incorrect DC") + 
      COUNTIFS([DC - Note]:[DC - Note], (CONTAINS("duplicate", @cell))) + 
      COUNTIFS([DC - Note]:[DC - Note], (CONTAINS("dc", @cell)))
    
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @Tiffany Sablan 

    Hope you are fine, please try the following formula ( and you can add as much as you need of the searching criteria in the Criteria column after you converting the formula to column format formula:

    =IF(ISBLANK(Criteria@row), "", COUNTIFS([DC - Status]:[DC - Status], HAS(@cell, Criteria@row)))

    the following screen shot shows the result


    PMP Certified

    [email protected]

    ☑️ 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
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓


    PMP Certified

    [email protected]

    ☑️ 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

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓

    Looks like a syntax error. You have an unnecessary comma.

    Try the corrected formula.

    = COUNTIFS([DC - Status]:[DC - Status], "Incorrect DC") + 
      COUNTIFS([DC - Note]:[DC - Note], (CONTAINS("duplicate", @cell))) + 
      COUNTIFS([DC - Note]:[DC - Note], (CONTAINS("dc", @cell)))
    
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @Tiffany Sablan 

    Hope you are fine, please try the following formula ( and you can add as much as you need of the searching criteria in the Criteria column after you converting the formula to column format formula:

    =IF(ISBLANK(Criteria@row), "", COUNTIFS([DC - Status]:[DC - Status], HAS(@cell, Criteria@row)))

    the following screen shot shows the result


    PMP Certified

    [email protected]

    ☑️ 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"

  • @Toufong Vang Thank you so much! That did the trick. @Bassam Khalil I went with the original formula but this example was extremely helpful for another formula I was trying to solve. I appreciate both of your quick responses.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓


    PMP Certified

    [email protected]

    ☑️ 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!