Using "*xxx*" in an array formula

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.
 
O

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...
 
M

man4mac

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

man4mac

a7n9 said:
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?
 
A

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top