Formula help recognising multiple words

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

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?
SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…