Using "*xxx*" in an array formula
 

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
------------------------------------------------------------------------
man4mac's Profile: http://www.excelforum.com/member.php...o&userid=36535
View this thread: http://www.excelforum.com/showthread...hreadid=562899

Using "*xxx*" in an array formula
 

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


--
man4mac
------------------------------------------------------------------------
man4mac's Profile: http://www.excelforum.com/member.php...o&userid=36535
View this thread: http://www.excelforum.com/showthread...hreadid=562899

Using "*xxx*" in an array formula
 

Try:

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


--
a7n9


------------------------------------------------------------------------
a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149
View this thread: http://www.excelforum.com/showthread...hreadid=562899

Using "*xxx*" in an array formula
 

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
------------------------------------------------------------------------
oteixeira's Profile: http://www.excelforum.com/member.php...o&userid=35320
View this thread: http://www.excelforum.com/showthread...hreadid=562899

Using "*xxx*" in an array formula
 

oteixeira Wrote:
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)


--
man4mac
------------------------------------------------------------------------
man4mac's Profile: http://www.excelforum.com/member.php...o&userid=36535
View this thread: http://www.excelforum.com/showthread...hreadid=562899

Using "*xxx*" in an array formula
 

Did you try my solution?


--
a7n9


------------------------------------------------------------------------
a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149
View this thread: http://www.excelforum.com/showthread...hreadid=562899

Using "*xxx*" in an array formula
 

a7n9 Wrote:
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?


--
man4mac
------------------------------------------------------------------------
man4mac's Profile: http://www.excelforum.com/member.php...o&userid=36535
View this thread: http://www.excelforum.com/showthread...hreadid=562899

Using "*xxx*" in an array formula
 

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


--
a7n9


------------------------------------------------------------------------
a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149
View this thread: http://www.excelforum.com/showthread...hreadid=562899