Use text contained within Cell Reference in COUNTIFS function

Options

Hi, I'm trying to develop a "Countifs" function that counts the number of times a particular text string is referenced in a separate sheet.

In the separate sheet, the range is a multi-select drop-down box, so the cells that have multiple options selected are omitted because the formula is looking for the full text string.

Is there a way to use "Contains" with a reference to search for part of the cell?

I've tried to show an example - basically Sheet 1 is the record of what each customer bought in each week, and sheet 2 is the summary.

Given Smartsheet separates multiple entries by a space, is there any way to do this?


Best Answer

Answers

  • Sandhiya07
    Options

    Hi @timclare

    Hope You are doing Good and Safe.

    Yes we can use "Contains" like the below,

    So in your case it will be like,

    =COUNTIFS([Week 1]:[Week 1], CONTAINS("Fruit", @cell))

    So you can refer other sheet instead of directly specifying column name.

    Hope this helps.

    Thanks,

    Sandhiya P

  • timclare
    Options

    Thanks Sandhiya, I am well thanks and hope you are too.

    I was wondering if it's possible to use a reference instead of the actual text within the formula. So in your case instead of "Updated" I would like to use a reference to a cell that contains "Updated".

    So in my case, instead of CONTAINS("Fruit") I was hoping to use CONTAINS([**REFERENCE**]) but I don't think this works.

    Any ideas?

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

    Hi @timclare

    I hope you're well and safe!

    Yes, that should work.

    =COUNTIFS([Week 1]:[Week 1], CONTAINS(Reference@row, @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 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.

  • timclare
    Options

    Great thanks heaps, that worked. I missed the final , "@cell" component of the CONTAINS() function

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

    @timclare

    Excellent!

    Happy to help!

    Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful. 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.

  • JSNelan
    JSNelan ✭✭
    Options

    I am using a Countifs formula to count how many times workshops are requested by state. I pull the date in from a separate sheet. I am able to drag the formula down for each state. However, I have to manually type in the state abbreviation for each state into each formula “AL”, etc. how do I change the “” to just pull the text from the row I am on?


  • JSNelan
    JSNelan ✭✭
    Options

    meant to say: I am using a Countifs formula to count how many times workshops are requested by state. I pull the DATA in from a separate sheet. I am able to drag the formula down for each state. However, I have to manually type in the state abbreviation for each state into each formula “AL”, etc. how do I change the “” to just pull the text from the row I am on?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!