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

mel_

Kelly Moore

Hey @mel_

This will put the 1 in the column (or check a checkbox) for each row, your formula is:

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

If you want to see the total count that goes in your summary sheet:

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

Note: If you want to exclude any other choices from also being present with "eat" and "scale", you will need to also filter on the number of responses in the cell. In other words, if "bread" was also a possible response, either of the formulas above would count "bread", "scale" and "eat" in your counts.

The COUNTM() function counts responses so if you set this to two, you could only have the above responses (if that is what you desire)

=IF(AND(HAS(Description@row, "eat"), HAS(Description@row, "scale"), COUNTM(Description@row)=2), 1)

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

Will any of these work for you?

Kelly

mel_

Hi Kelly! Thanks so much for your help. The first formula, =IF(AND(HAS(Description@row, "eat"), HAS(Description@row, "scale")), 1), returned a blank cell (when description had both "eat" and "scale").

=IF(AND(HAS(Description@row, "eat"), HAS(Description@row, "scale"), COUNTM(Description@row)=2), 1) also returned a blank.

The third, =COUNTIFS(Description:Description, AND(HAS(@cell, "eat"), HAS(@cell, "scale"),COUNTM(Description@row)=2)), returned a 0.

It may help if I elaborate on the use case. The cells in the description row contain blocks of text, like:

"I was trying to measure the chicken I was going to eat, but the scale didn't work."

I want to be able to mark if both my flag words are present in the block of text, so if the Description in the row is:

"I was trying to measure the chicken I was going to eat, but the scale didn't work." Should return 1.

If the Description in the row is: "I was trying to measure the chicken I was using, but the scale didn't work." Should return 0.

If the Description in the row is: "I was eating dinner and the dog barked." I want it to return 0 even though "eat" is there, it has "ing" attached so I don't want it to be counted.

Does that make sense?

Thank you!!!

mel_

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.