excelexcel-formula

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

Solution

  • 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