COUNTIFS of a text string based on a condition

Options

I have two text columns. One column contains titles of documents. The second column contains specifies the document's "category." One of the categories in the "Category" column is "Presentations." However, in that category are two different types of presentation: "BDM" and "TDM". Whether a document is a BDM or TDM is called out in its title.

I need to count the total number each of BDM and TDM. Normally, I would use something like:

=COUNTIFS(Category:Category, "Presentations", Title:Title, "*BDM*")

But, Smartsheet does not recognize wildcard characters. I tried using this:

=IF(CONTAINS("BDM", Title:Title), COUNTIF(Category:Category, "Presentations")

but I got 0 results (which I know is not accurate).

I also tried a suggestion from another thread:

=COUNTIFS(Category:Category, FIND("BDM", Title:Title))>0) but got an error.

Lastly, I tried:

=IF(CONTAINS(Title:Title, "BDM"), COUNTIF(Category:Category, "Presentations")) - got another error.


Any suggestions much appreciated!

Best Answer

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    Answer ✓
    Options

    Thanks, Andrée - unfortunately, that did not work. It gave me a result of 0, but I know the correct numbers are 45 and 43, respectively.

    To get the count, I added a hidden column to the sheet that enters a 1 whenever the string "BDM" appears in the title, then just did a sum on that column. Did the same for "TDM." Seemed the easiest work around, although this all would be easily solved if Smartsheet accommodated wildcards (like "*").

    Appreciate the reply.

Answers

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    Options

    I should add that I can't change the data in the "Category" column - this is imported from another system.

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

    Hi Danielle,

    Try something like this.

    =COUNTIFS(Title:Title; CONTAINS("BDM"; @cell); Category:Category; "Presentations")

    The same version but with the below changes for convenience.

    =COUNTIFS(Title:Title, CONTAINS("BDM", @cell), Category:Category, "Presentations")
    

    Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

    Did that work?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as 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.

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    Answer ✓
    Options

    Thanks, Andrée - unfortunately, that did not work. It gave me a result of 0, but I know the correct numbers are 45 and 43, respectively.

    To get the count, I added a hidden column to the sheet that enters a 1 whenever the string "BDM" appears in the title, then just did a sum on that column. Did the same for "TDM." Seemed the easiest work around, although this all would be easily solved if Smartsheet accommodated wildcards (like "*").

    Appreciate the reply.

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

    Happy to help!

    Strange! It should work.

    I'd be happy to take a look at it if you want.

    Can you maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)


    Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as 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.

  • H.Arsenault
    H.Arsenault ✭✭✭
    Options

    @Andrée Starå - Did you find a fix for this? I am in need of a similar formula and am getting the same "0" response when I know that to be inaccurate.

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    Options

    @H.Arsenault - I don't know if Andrée has a different suggestion, but I solved my issue by adding a column to the sheet that enters a 1 whenever the string "BDM" appears in the title:

    =IF(CONTAINS("BDM", Title@row), 1, 0)

    Then, I just did a sum on that column to get my total number of BDM presentations.

    Did the same for "TDM."

    I hid both "helper" columns on the sheet to reduce clutter.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!