contains and CountIf formula

Options

Good day, I want to the the following formula in my sheet summary.

I want to know how many enquiries there where in 2023 (The ref Number for 2023 Enquiries are "EC-xxx")

The REF colomn is an auto-number format.

I want to count the Ref column if it contains EC.

Please assist with this formula?

=COUNTIF(REF:REF; Contains(REF:REF;"EC) it says invalid

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 12/12/23
    Options

    Hey @Nicole Martin

    The @cell reference is used to tell a range (REF:REF in this case) to look at each cell and evaluate it.

    =COUNTIFS(REF:REF, CONTAINS("EC", @cell))

    Will this work for you?

    Kelly

  • Nicole Martin
    Options

    Thank you so Much!


    How will the forumla work when I have multiple Ifs?

    I would like to the the formula now when [=COUNTIFS(REF:REF, CONTAINS("EC", @cell))] and if the SUPPLIER column contains "LINX"?

    =COUNTIFS(SUPPLIER:SUPPLIER; ="LINX"; (REF:REF; CONTAINS("ED"; @cell)))

    Thank you for the assistance.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Nicole Martin

    I hope you're well and safe!

    Try something like this.

    =COUNTIFS(SUPPLIER:SUPPLIER; "LINX"; REF:REF; CONTAINS("ED"; @cell))

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!