SUMPRODUCT, ISNUMBER help

[bds]

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

 

[barry houdini]

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]

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]

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.

 

[bds]

thank you

 

[bds]

=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!)

 

[PaddyD]

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]

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]

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]

That does the trick, Domski - thanks so much!