Excel SUMPRODUCT ISNUMBER SEARCH Range and IGNORE Empty Blank Cells In Range

0
votes

This is my problem setup in Excel.

My data

In column G, notice that my formula is referencing a range in column D and that range contains empty / blank cells. When this happens, the formula always returns TRUE.

Look at Column G

In column H, when the range referenced doesn't include empty / blank cells, it returns the correct answer FALSE indicating that none of the words in column D are in the string value in column F.

Look at Column H

My Question

How can I modify the formula below to ignore blank cells in a range and correctly return FALSE if none of the values in a range D is in the string value in column F:

=SUMPRODUCT(--ISNUMBER(SEARCH(Rank!$D$2:$D$24,F2)))>0
1
excelexcel-formulasumproduct

1 Answers

1
votes

Perhaps like this:

=SUMPRODUCT(--(ISNUMBER(SEARCH(D$2:$D$24,F2))*($D$2:$D$24<>"")))>0

enter image description here

Or slightly more simple:

=SUMPRODUCT(ISNUMBER(SEARCH(D$2:$D$11,F2))*($D$2:$D$11<>""))>0