# Sumproduct Isnumber Search is returning same result for different values

1. ## Sumproduct Isnumber Search is returning same result for different values

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)

THANKS!

2. ## Re: Sumproduct Isnumber Search is returning same result for different values

Hi ,

Try this :

Formula:
`Please Login or Register  to view this content.`

The changes are highlighted.

This is an array formula , to be entered using CTRL SHIFT ENTER.

Narayan

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1