# Sumproduct Isnumber Search is returning same result for different values

#### labruzzi

##### New Member
Hello - I utilizing a formula that is intended to count the number of visible entries in a table that meets multiple criteria utilizing Sumproduct, Isnumber, and Search. However; I have just realized that in some instances the formula is including results it should not.

For example, in the below forumla, to be counted, the row needs to contain "X" in column (E), contain "F" in column (K), and contain "2" in column (H) - the formula should also only be counting visible results. However, it looks like if column (H) has the number "12" it is counting it. Similarly if I adjust the formula to search for "7" in column (H), it will count those that have "17".

How can I adjust the formula to count only exact matches for "2" or "7" in column (H)?

=SUMPRODUCT(SUBTOTAL(3,OFFSET(\$F\$11:\$F\$23733,ROW(\$F\$11:\$F\$23733)-MIN(ROW(\$F\$11:\$F\$23733)),,1)),ISNUMBER(SEARCH("X",E11:E23733))+0,ISNUMBER(SEARCH("F",\$K\$11:\$K\$23733))+0,ISNUMBER(SEARCH("2",\$H\$11:\$H\$23733))+0)

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### JoeMo

##### MrExcel MVP
If there are spaces on either side of the 2 (or 7) change your search to:

SEARCH(" 2 ",...)

#### labruzzi

##### New Member
No, the entries for column (H) are all numerical values between 2-17.

#### JoeMo

##### MrExcel MVP
No, the entries for column (H) are all numerical values between 2-17.
Then replace the ISNUMBER(SEARCH ...) with this:

(\$H\$11:\$H\$23733=2)

1,089,422
Messages
5,408,139
Members
403,186
Latest member
123hpeinstall