Contain items that I don't want to count

Craig Lemberger

"pruned boxwood and boxwood hedges."

This is an an entry for a cell from a form. There are two search items here: "boxwood" and "boxwood hedges".

I have another cell attempting to note that boxwood (shrubs) were pruned separate from "boxwood hedges".

=IF(AND(CONTAINS("boxwood", [FN: Pruning]@row), (NOT(CONTAINS("boxwood Hedge", [FN: Pruning]@row))), Month@row > 5, Month@row < 11), 1, "")

of course this only brings nothing if the entry has "boxwood hedge".

If I only do the following (disregard the dates parameters)

IF(CONTAINS("boxwood", [FN: Pruning]@row), 1, ""), I get boxwood even if I only pruned boxwood hedges.

Up shot: how do I account for boxwood only when boxwood hedges are also noted?

Nitish K

Hi

So as far as how I have understood your query I have given my solution here below

Consider that u have a column named as Shrubs with your data that is this one

pruned boxwood and boxwood hedges

What u can do is that u can create a column with checkbox type and enter the formula like this one

=IF(CONTAINS("boxwood", LOWER(Shrubs@row)), 1, 0)

Explanation: This formula will consider boxwood only irrespective of the case how the data is entered in the sheet through form.

You can see my output below. I hope this solves your query, if not let me know

Craig Lemberger

Thank you @Nitish K. I appreciate your explanation and time. "Lower" is to make letter lowercase? No? Anyway, my problem is that if the cell only says boxwood hedges, my boxwood box will be checked as well as the boxwood hedge. I need the boxwood box to NOT check if it only says "boxwood hedges".

Paul Newcome

Are you able to provide screenshots for context?