Seeking a formula that counts specific characters in a cell.

Options

My project task sheet has parent rows for each department and child row for each project within the parent department. Each project row has a LEAD DEPT column with dropdown options that show the department acronym and business unit---DEPARTMENT (Unit).

The LEAD DEPT options are;

  • DCM (Marketing)
  • DCM (Membership)
  • DCM (Publications)
  • LEARNING (MCET)
  • LEARNING (PD)

I am trying to create a "COUNTIF" formula that will count the total number of rows where the LEAD DEPT includes the acronym "DCM" and ignores the units. Using the options above, I want a formula that returns a value of "3".

Tags:

Best Answers

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

    Hi @Kris

    Hope you are fine, please try the following formula:

    =COUNTIFS(department:department, CONTAINS("DCM", @cell))
    

    The following screenshot shows the result:


    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"

  • Kris
    Kris ✭✭✭
    Answer ✓
    Options

    It worked great. Thank you!

Answers

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

    Hi @Kris

    Hope you are fine, please try the following formula:

    =COUNTIFS(department:department, CONTAINS("DCM", @cell))
    

    The following screenshot shows the result:


    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"

  • Kris
    Kris ✭✭✭
    Answer ✓
    Options

    It worked great. Thank you!

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 09/26/21
    Options

    @Kris 

    Excellent, i will be happy to help you any time.

    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!