Using "*xxx*" in an array formula

  • Thread starter Thread starter man4mac
  • Start date Start date
M

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.
 
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...
 
oteixeira said:
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...
Click to expand...
Thanks, but I need it to check 2 criteria at a time, and be totaly
dynamic (I don't have to do anything)
 
a7n9 said:
Try:

=SUMPRODUCT(--ISNUMBER(SEARCH("jim",A1:A6)),--(B1:B6="Phone"))
Click to expand...
Thanks I think this works! How does it work though, I can't find
ISNUMBER in the excel reference. Whats going on here?
 
=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.
 
Back
Top