# SUMPRODUCT, ISNUMBER help

#### bds

##### Board Regular
Here is the formula I am using:

=SUMPRODUCT(--ISNUMBER(SEARCH("P*",hands!\$L\$3:\$L\$64500,(--ISNUMBER(SEARCH("call*",(hands!\$K\$3:\$K\$64500),(--ISNUMBER(SEARCH("*fold*",hands!\$Q\$64500)))))))))

I am trying to find out how many rows of data have:

"P" in column L AND "call*" in column K AND "*fold*" in column Q.

I am getting zero as an answer and that is wrong.

Can anyone tell me how to change the formula to get the correct answer? Thanks

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### barry houdini

##### MrExcel MVP
Are you using the * as a wildcard? If so you probably don't need these within the SEARCH function, perhaps

=SUMPRODUCT(--ISNUMBER(SEARCH("P",hands!\$L\$3:\$L\$64500)),--ISNUMBER(SEARCH("call",hands!\$K\$3:\$K\$64500)),--ISNUMBER(SEARCH("fold",hands!\$Q\$3:\$Q\$64500)))

#### bds

##### Board Regular
This solution works perfectly! Thanks so much.

Yes, the * were wild cards.

Can you explain why the -- belong outside the parenthesis? I had used a similar formula but with only two ISNUMBER SEARCH in it and they were inside the perenthesis and worked fine.

#### Scott Huish

##### MrExcel MVP
In this case, the parenthesis are not absolutely necessary around each ISNUMBER, but generally the -- should go outside the parentheses because you are trying to coerce the value of the boolean expression to 1 or 0, not the value of a cell for example.

Try this:

Put the number 7 in A1.
In B1, put = --A1=7, this return TRUE, not what we want, we're not trying to coerce A1 to be something we're trying to coerce A1=7 to be 1 or 0, thus --(A1=7) in B1 would return the correct result.

thank you

#### bds

##### Board Regular
=SUMPRODUCT(--ISNUMBER(SEARCH("P",hands!\$L\$3:\$L\$64500)),--ISNUMBER(SEARCH("call",hands!\$K\$3:\$K\$64500)),--ISNUMBER(SEARCH("fold",hands!\$Q\$3:\$Q\$64500)))

Now, what I would like to do is the above function, BUT what I want to know is the sum of numbers in another column when all of the things in the above formula are true. How would I go about doing that?

(Thanks for the ongoing tutorial ladies and gents!)

##### MrExcel MVP
Quote:

=SUMPRODUCT(--ISNUMBER(SEARCH("P",hands!\$L\$3:\$L\$64500)),--ISNUMBER(SEARCH("call",hands!\$K\$3:\$K\$64500)),--ISNUMBER(SEARCH("fold",hands!\$Q\$3:\$Q\$64500)),range_to_sum)

#### bds

##### Board Regular
Quote:

=SUMPRODUCT(--ISNUMBER(SEARCH("P",hands!\$L\$3:\$L\$64500)),--ISNUMBER(SEARCH("call",hands!\$K\$3:\$K\$64500)),--ISNUMBER(SEARCH("fold",hands!\$Q\$3:\$Q\$64500)),range_to_sum)

Paddy - thank you! This worked perfectly.

Next question,

suppose I want to find rows where in one of the columns there is a blank cell? I tried using "", and that doesn't work - it gives me all the rows. So, instead of the "P" in the function above, what would I use to indicate that I want cells with absolutely nothing in them instead of cells with a "P" in them?

#### Domski

##### Well-known Member
Like this:

=SUMPRODUCT(--ISBLANK(hands!\$L\$3:\$L\$64500),--ISNUMBER(SEARCH("call",hands!\$K\$3:\$K\$64500)),--ISNUMBER(SEARCH("fold",hands!\$Q\$3:\$Q\$64500)),range_to_sum)

Dom

#### bds

##### Board Regular
That does the trick, Domski - thanks so much!

Replies
5
Views
190
Replies
6
Views
218
Replies
2
Views
208
Replies
7
Views
205
Replies
10
Views
829

1,190,782
Messages
5,982,886
Members
439,803
Latest member
sushilneupane

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back