Formula help recognising multiple words

dave.mcpherson56751

Hi,

I have a formula that looks for a specific word or variance of the word, is it possible to look for more than one word?

The formula i have is =COUNTIFS(Response1, FIND("shower", LOWER(@cell)) > 0)

This works for the word showers or shower which is perfect, however am i am able to count if the cell contains either "shower" or "Clean"

Appreciate any help or guidance.

As always thank you in advance

Dave

Eid E. Eid

I would try this, not sure, but just my 2 cents...

 

=COUNTIFS(Response1, OR(FIND("shower", LOWER(@cell)) > 0,FIND("clean", LOWER(@cell)) > 0))

 

 

Andrée Starå

Hi Dave,

Eid's formula will work. (Nicely done Eid)

To add. I've added @row and also the structure for different regions.

Try something like this.

=COUNTIFS(Response@row; OR(FIND("shower"; LOWER(@cell)) > 0; FIND("clean"; LOWER(@cell)) > 0))

The same version but with the below changes for your and others convenience.

=COUNTIFS(Response@row, OR(FIND("shower", LOWER(@cell)) > 0, FIND("clean", LOWER(@cell)) > 0))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

dave.mcpherson56751

Thank you both for taking the time to help.

Once again the community proves invaluable.

Regards

 

Dave 

Paul Newcome

You can also use CONTAINS instead of FIND which removes the need for UPPER vs LOWER functions since CONTAINS is not case sensitive.

Andrée Starå

Happy to help!

Andrée Starå

That's a good point for using CONTAINS because of it not being case sensitive.

Paul Newcome

Really it depends on the use case. I use still use the FIND function sometimes because I CAN include case sensitivity if needed and the CONTAINS function (as I explained above) lets you cut out UPPER and LOWER functions.

 

Overall, the CONTAINS function has been a very excellent addition.

Andrée Starå

Yes, it depends on the use case. I can't wait for more formulas to get released.