We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

find text in cell from a filter list; COUNTIF

asked 2020-08-31 01:51:48 +0200

cybersurfer5000 gravatar image

updated 2020-09-01 16:49:52 +0200

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. image description

It does not work for "pan" or "chocolate". Regular expressions are enabled. image description image description

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:

image description

Here is the actual ODS file: C:\fakepath\example.ods

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

image description

C:\fakepath\exampleSophia1.ods

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
2

answered 2020-08-31 06:25:56 +0200

JohnSUN gravatar image

updated 2020-08-31 06:34:13 +0200

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

FormulaArray.png

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.

wildcards.png

edit flag offensive delete link more

Comments

Thank you! :D

cybersurfer5000 gravatar imagecybersurfer5000 ( 2020-08-31 07:52:47 +0200 )edit

@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.

cybersurfer5000 gravatar imagecybersurfer5000 ( 2020-08-31 08:53:56 +0200 )edit

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 - C:\fakepath\example_Sophia.ods

JohnSUN gravatar imageJohnSUN ( 2020-08-31 10:11:20 +0200 )edit

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!

cybersurfer5000 gravatar imagecybersurfer5000 ( 2020-09-01 16:45:04 +0200 )edit

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. C:\fakepath\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 [C:\fakepath\exampleSophia1.ods](/upfiles/1598973093643539.ods))

JohnSUN gravatar imageJohnSUN ( 2020-09-01 17:18:53 +0200 )edit

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

cybersurfer5000 gravatar imagecybersurfer5000 ( 2020-09-02 06:02:58 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-08-31 01:51:48 +0200

Seen: 91 times

Last updated: Sep 01 '20