COUNTIFS with HAS two words/phrases in same cell

Options

Hi! I am trying to write a formula to count ONLY when the cell in the description column has BOTH "eat" AND "scale". I want to use HAS because sometimes I want to search for an acronym and I think it is counting other combinations of letters when I use CONTAINS. If the description @ cell has both phrases, I want a 1 to appear so I can put the total in the sheet summary.

I know the syntax of these isn't right, but to give you an idea:

=COUNTIFS(Description:Description, HAS(@cell, "eat")) AND (Description:Description, HAS(@cell, "scale")) 

=COUNTIFS(Description:Description, HAS(@cell, ("eat" AND "scale"))

=COUNTIF(HAS(([Description]: [Description], "EAT"), AND(HAS([Description]: [Description], "scale") "yes", “no”)

Also, similarly, I was using =COUNTIFS(Description@row, FIND("scale", @cell) > 0)

but I am unsure if it is serving the purpose I am looking for it to. I want to return a 1 if "scale" is in the Description. How would I change this to HAS? Something like below?

=IF(HAS(Description@cell, "ERN"), "1", “0”)

So I would love to know the right way to use COUNTIFS and HAS for 1 phrase and 2 phrases, to return a 1 or 2, or even true or false, just something I can total summarize easily.

Thanks so much in advance!!

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @mel_

    If you were using multi-select columns, then HAS would be the way to go. HAS will not work to look for values within a Text/Number field unless you're only wanting to match the entire cell contents.

    Example: =IF(HAS([Day of Week]: [Day of Week], "Monday"), "yes", “no”)

    In the example above, if [Day of Week]1 is in a Text/Number column, HAS will return “true” if [Day of Week]1 = “Monday” but will return “false” if [Day of Week]1 = “Monday Tuesday.” If [Day of Week]1 is in a multi-select column, HAS will return “true” if [Day of Week]1 = “Monday” and will also return true if [Day of Week]1 contains values of “Monday” and “Tuesday”. It will return “false” if the value in [Day of Week]1 = “Monday Tuesday”. 

    In the example you gave, it's not enough to simply look for the strings "Eat Well" and "scale" and get accurate results; In your case, you need to actively exclude the string "ScaleLess" as well.

    Since Smartsheet formulas work from left to right, we'll exclude the "ScaleLess" string first by using NOT(CONTAINS), then set the criteria for the other two strings:

    =IF(AND(NOT(CONTAINS("ScaleLess", Description@row)), CONTAINS("Eat Well", Description@row), CONTAINS(" scale", Description@row)), 1, 0)


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    Hi @mel_

    HAS is used for exact matches, so CONTAINS should work in this instance.

    If you want a column formula then this should work:

    =IF(AND(CONTAINS("eat", Description@row), CONTAINS("scale", Description@row)), 1, 0)

    You can just skip it if you're wanting a sheet summary though:

    =COUNTIFS(Description:Description, CONTAINS("eat", @cell), Description:Description, CONTAINS("scale", @cell))

    Example data:

    I'm not sure what issues you are having with data - can you provide some examples where the above would provide a false positive?

  • mel_
    mel_ ✭✭
    Options

    Hey there, Nick! Thank you so much for your help!

    So the issue with the =IF(AND(CONTAINS("eat", Description@row), CONTAINS("scale", Description@row)), 1, 0)

    is that I want only exact matches. So for example, I want only the first row in my screenshot to return 1. I want the 2nd and 3rd rows to return 0 since they aren't exact matches- they both have something added to the end. Because they reference different programs or no program at all, they will mess up my ability to recognize trends in the data related to JUST the Eat Well program scale issue.

    Does that make sense? Thanks again!!


  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @mel_

    If you were using multi-select columns, then HAS would be the way to go. HAS will not work to look for values within a Text/Number field unless you're only wanting to match the entire cell contents.

    Example: =IF(HAS([Day of Week]: [Day of Week], "Monday"), "yes", “no”)

    In the example above, if [Day of Week]1 is in a Text/Number column, HAS will return “true” if [Day of Week]1 = “Monday” but will return “false” if [Day of Week]1 = “Monday Tuesday.” If [Day of Week]1 is in a multi-select column, HAS will return “true” if [Day of Week]1 = “Monday” and will also return true if [Day of Week]1 contains values of “Monday” and “Tuesday”. It will return “false” if the value in [Day of Week]1 = “Monday Tuesday”. 

    In the example you gave, it's not enough to simply look for the strings "Eat Well" and "scale" and get accurate results; In your case, you need to actively exclude the string "ScaleLess" as well.

    Since Smartsheet formulas work from left to right, we'll exclude the "ScaleLess" string first by using NOT(CONTAINS), then set the criteria for the other two strings:

    =IF(AND(NOT(CONTAINS("ScaleLess", Description@row)), CONTAINS("Eat Well", Description@row), CONTAINS(" scale", Description@row)), 1, 0)


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!