COUNTIFS with HAS two words/phrases in same cell

mel_

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!!

Jeff Reisman

@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)

Nick Korna

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_

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

@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)

mel_

@Jeff Reisman Thank you!!!