Find text in cell from a filter list; COUNTIF

[cybersurfer5000]

Hello guys!

I try to find any of the text in the filter list (E2:E4) within cell A2. The formula I found is returning “1” only for the first word in the filter list, i.e. kni.

It does not work for “pan” or “chocolate”. Regular expressions are enabled.

I appreciate the help as always!
Sophia

FOLLOW UP QUESTION:
This is what I have in mind: an accounting sheet with categories assigned to transactions from my bank account.

Categories are Food, Rent, Car, Phone, Shopping, with filter words in each of the categories.

I want the formula to search C colume, starting at C9, and assign a catogory in D colume same row (pink arrow) based on the work of what filter list was found. E.g. if the description contains “cafe”, category “Food” is assigned. If the description contains “maverik”, category “Car” is assigned. Etc. Please see img and ODS file below:

Here is the actual ODS file: example.ods

EXAMPLE FILE 2: the array formula does not work after adding two new inputs (cafe, elevate) in the C colume.

exampleSophia1.ods

[JohnSUN]

It’s simple. The fact is that COUNTIF() usualy has a different set of parameters - first the search range, and then the condition (the desired value). In other words, your formula looks for each value from E2: E4 in the text of cell A2.

To see the real result, enter your formula as an array formula (complete the entry Ctrl+Shift+Enter). And you will see something like this

This means that your formula is working correctly, but you always see only the first cell of the entire array of results.

To make the formula count correctly, it is enough to wrap it in another function, in SUMPRODUCT():

=SUMPRODUCT(COUNTIF(A2;"*" & $E$2:$E$4 & "*"))

By the way, I see that you are using an asterisk, wildcards, and not regular expressions?

Please, make sure that this parameter is set correctly for you - this discrepancy can lead to many unpleasant disappointments.

[cybersurfer5000]

Thank you!

[cybersurfer5000]

@JohnSUN, aw I am still struggling. The example you gave does work great, however I can’t make it work for multiple filter lists.
This is what I have in mind: an accounting sheet with categories assigned to transactions from my bank account. I can make it work with a combination of ISNUMBER and SEARCH functions, but its clunky and the formula is massive in size!
I added a description of the problem, ODF file and img to the original post under paragraph “FOLLOWUP QUESTION”, hoping you could have a peak and give a hint what I should change to make it work with the COUNTIF and SUMPRODUCT formula you suggested, which seems so much more elegant.

[JohnSUN]

First of all I would change the list of categories and keywords (see ‘dictionary’ sheet). This makes the search formula much easier.

Just in case, I would not return the first encountered value, but all found categories - this will allow you not to accidentally miss any of the options. For example, the keyword cafe can be in two categories at once (food and shopping - why not? You drank coffee in a shopping center). So I used the TEXTJOIN() function.

Unfortunately, the SUMPRODUCT() trick will not work in combination with TEXTJOIN(), the formula must be entered as an array formula in one cell, and then copied to the entire column.

Okay, check out this sample and ask more if you don’t understand something - example_Sophia.ods

[cybersurfer5000]

Thanks for the example @JohnSUN. I added 2 lines (cafe, elevate) to the bottom of the C colume. I did not alter the array formula. However, the array formula returns the result of C2 for all other rows as well. I added image and example file to the original post. Question 1: what went wrong?
Question 2: How can I add an image and file path to a comment here in the forum?

Thank you in advance!

[JohnSUN]

Perhaps I did not explain well - the first cell with the formula should not be stretched to the entire column, but copied and pasted into all cells of the column. When we stretch a cell, we simply enlarge the area to output one formula. When we copy and paste, we paste in multiple copies of the first formula.
exampleSophia1.ods

Answer 2 I go into editing my answer (or question), use the usual tools for inserting files or images, cut the resulting strings, click Cancel so as not to spoil the existing answer, and paste the copied rows into the comment. (for example, here I paste text [exampleSophia1.ods|attachment](upload://zXOCxTjvj4B0TO0udGcC81HUKCq.ods))

[cybersurfer5000]

Aaahh I see. It works great now! Thank you @JohnSUN
Yeah, thats clever (-> answer 2)