Using "*xxx*" in an array formula

[man4mac]

Hi everyone,

Quick question. I have this array formula:

{=SUM(IF(Sheet1!A1:A6="Jim",IF(Sheet1!B1:B6="Phone",1,0),0))}

And I'll quickly show you what the chart looks like:
A B
Jim Breather Phone
Bill Fax
Jim Beezley Phone
John Phone
Bill Car
Jim Looser Car

Now what I want to do is count how many times a person named "Jim" sold
a phone. Its easy to do if all you have entered is "Jim", but I want it
to find the substring "jim" and then count it. If there was only 1
criteria I would use
COUNTIF like this:

=COUNTIF(A1:A6,"*jim*")
But in an array function (for multiple criteria) the little * don't
work. They actually count as part of the text. Any help would be just
great.

 

[man4mac]

anyone? sorry normally I wouldn't bump my post, but I really need thi
for work

 

[a7n9]

Try:

=SUMPRODUCT(--ISNUMBER(SEARCH("jim",A1:A6)),--(B1:B6="Phone"))

 

[oteixeira]

Hi. I don't know if this is what you want but you can:

Write "jim" on cell. let's say B2 and re-write your formula to:

=COUNTIF(A1:A6,"B6")

You can put anything into B6, once at a time, for shure...

 

[man4mac]

Hi. I don't know if this is what you want but you can:

Write "jim" on cell. let's say B2 and re-write your formula to:

=COUNTIF(A1:A6,"B6")

You can put anything into B6, once at a time, for shure...

Thanks, but I need it to check 2 criteria at a time, and be totaly
dynamic (I don't have to do anything)

 

[a7n9]

Did you try my solution?

 

[man4mac]

Try:

=SUMPRODUCT(--ISNUMBER(SEARCH("jim",A1:A6)),--(B1:B6="Phone"))

Thanks I think this works! How does it work though, I can't find
ISNUMBER in the excel reference. Whats going on here?

 

[a7n9]

=SUMPRODUCT(--ISNUMBER(SEARCH("jim",A1:A6)),--(B1:B6="Phone"))

Let's break down in to pieces;

B1:B6="Phone" would create an array of TRUE and FALSE according to the
values in the range B1:B6. By coercing it by double negation (--),
we'll get an array of 0s and 1s.

SEARCH("jim",A1:A6) would create an array of the positions where it
found jim in the range A1:A6, if it didn't find it would return a VALUE
error, therefore, we check it by ISNUMBER function if the returned value
is a number or not, which would create an array of TRUE and FALSE and
again using double negation (--), we'll get and array of 0s and 1s.

SUMPRODUCT will just giving the summation of the product of these two
arrays.

Excel help should provide info on ISNUMBER function.