CODEWITHSUNDEEP
excelexcel-formula
user007
user007

Reputation: 1192

Using COUNTIF function with substring functionality or custom comparation

I am able to color/run logic for the column B using COUNTIF function: (I am checking if element B is substring of any element A)

=COUNTIF($A$1:$A$10, ""&B1&"")>0

test1a  a   TRUE
test2b  b   TRUE
test3cd c   TRUE
e       g   FALSE
f       h   FALSE

If I want to use this formula opposite way (I need this for color formatting) I would like to do something like this:

=COUNTIF(A1, ""&($B$1:$B$10)&"")>0

Is it possibe to apply custom comparation for COUNTIF function

Better example (1,0 is what I could achieve, True/False is what I needed - has been answered below):

test1a  a   1   TRUE
test2b  xx  0   TRUE
test3cd b   1   FALSE
e       g   0   FALSE
f       h   0   FALSE

Upvotes: 1

Views: 591

Answers (4)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60464

When the criteria argument is an array of values, the formula will return an array. You then have to SUM the result to get the number of matches. eg:

=SUMPRODUCT(COUNTIF(A1,"*"&$B$1:$B$5&"*"))>0

SUMPRODUCT allow you to do this without entering it with ctrl+shift+enter

If you don't mind the CSE sequence, you can use something like

=SUM(COUNTIF(A1,"*"&$B$1:$B$5&"*")) > 0

or even:

=OR(COUNTIF(A1,"*"&$B$1:$B$5&"*"))

in Excel 0 = FALSE and the positive integers = TRUE

Upvotes: 1

jblood94
jblood94

Reputation: 17001

Enter it as an array formula (ctrl+shift+enter):

=COUNTIF(A1,"*"&B1:B10&"*")

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96791

Use the array formula:

=LEN(TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH(B1:B10,A1)),B1:B10,"")))>0

enter image description here

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.

In this case we get True because the string in A1 contains the word big.

Upvotes: 0

Suraj Shourie
Suraj Shourie

Reputation: 2605

If any element from B is substring of my current element A => isn't this formula working?

COUNTIF($B$1:$B$10, "*"&A1&"*")>0

Upvotes: 0

Related Questions