CODEWITHSUNDEEP
excelexcel-formulasumproduct
Jarad
Jarad

Reputation: 18883

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

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

Upvotes: 0

Views: 1995

Answers (1)

BigBen
BigBen

Reputation: 49998

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

Upvotes: 1

Related Questions