I could use an "array formula" suggestion on my problem..........

I've created a database in Excel, and I am trying to use the array formula
below to count for me, but it is only working if I use a single search
criteria in Row AN (e.g. AC Initiated or Referred to Tx) but not both.

What I'd like to do (as the formula is attempting to execute) is count all
client's who were seen during the 2007 calendar year, and identify those 2007
client's who were "referred to tx" and those who began tx (e.g. AC
Initiated). I didn't know if an Array formula can search for multiple
criteria in the same row twice (once for all 2007 clients who were "Referred
to Tx" and then again for all who initiated tx (AC Initiated). I've tried
several variations of the formula below with no success. Any suggestions?
Thank you in advance, Dan

The Formula I was using that isn't working is:

=SUM((--(YEAR(Z4:Z3500)=2007))*(AN4:AN3500="AC
Initiated")*(AN4:AN3500="Referred to Tx"))

Try this sumproduct alternative, normal ENTER will do:
=SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(AN4:AN3500,{"AC
Initiated";"Referred to Tx"},0)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dan the Man" wrote:
> I could use an "array formula" suggestion on my problem..........
>
> I've created a database in Excel, and I am trying to use the array formula
> below to count for me, but it is only working if I use a single search
> criteria in Row AN (e.g. AC Initiated or Referred to Tx) but not both.
>
> What I'd like to do (as the formula is attempting to execute) is count all
> client's who were seen during the 2007 calendar year, and identify those 2007
> client's who were "referred to tx" and those who began tx (e.g. AC
> Initiated). I didn't know if an Array formula can search for multiple
> criteria in the same row twice (once for all 2007 clients who were "Referred
> to Tx" and then again for all who initiated tx (AC Initiated). I've tried
> several variations of the formula below with no success. Any suggestions?
> Thank you in advance, Dan
>
> The Formula I was using that isn't working is:
>
> =SUM((--(YEAR(Z4:Z3500)=2007))*(AN4:AN3500="AC
> Initiated")*(AN4:AN3500="Referred to Tx"))

Try this (normally entered):

=SUMPRODUCT((YEAR(Z4:Z3500)=2007)*(AN4:AN3500={"Referred to Tx","AC
Initiated"}))

Biff

"Dan the Man" <Dan the (E-Mail Removed)> wrote in message
news:F5DD01D1-14B7-4531-96AC-(E-Mail Removed)...
>I could use an "array formula" suggestion on my problem..........
>
> I've created a database in Excel, and I am trying to use the array formula
> below to count for me, but it is only working if I use a single search
> criteria in Row AN (e.g. AC Initiated or Referred to Tx) but not both.
>
> What I'd like to do (as the formula is attempting to execute) is count all
> client's who were seen during the 2007 calendar year, and identify those
> 2007
> client's who were "referred to tx" and those who began tx (e.g. AC
> Initiated). I didn't know if an Array formula can search for multiple
> criteria in the same row twice (once for all 2007 clients who were
> "Referred
> to Tx" and then again for all who initiated tx (AC Initiated). I've tried
> several variations of the formula below with no success. Any suggestions?
> Thank you in advance, Dan
>
> The Formula I was using that isn't working is:
>
> =SUM((--(YEAR(Z4:Z3500)=2007))*(AN4:AN3500="AC
> Initiated")*(AN4:AN3500="Referred to Tx"))

Thank you Max. The sumproduct alternative also works with "normal entry" as
you suggested. Sorry for the multiple posts, but I'm a "newbie" to using this
wonderful discussion group.

Got another question to throw out at you or Ron (both who offered workable
suggestions with normal entry or Array's).

I attempted to modify another formula (using the "normal entry" suggestion
from Max)without success. This formula has multiple search criteria (4). Here
is that formula:

=SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(S4:S3500,{"RF Hearing &
Red. Fee","MR Hearing & Red. Fee","Red Flag Hearing Client";"Medical Risk
Hearing"},0)))

Thank you in advance!

Dan

"Max" wrote:

> Try this sumproduct alternative, normal ENTER will do:
> =SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(AN4:AN3500,{"AC
> Initiated";"Referred to Tx"},0)))
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Dan the Man" wrote:
> > I could use an "array formula" suggestion on my problem..........
> >
> > I've created a database in Excel, and I am trying to use the array formula
> > below to count for me, but it is only working if I use a single search
> > criteria in Row AN (e.g. AC Initiated or Referred to Tx) but not both.
> >
> > What I'd like to do (as the formula is attempting to execute) is count all
> > client's who were seen during the 2007 calendar year, and identify those 2007
> > client's who were "referred to tx" and those who began tx (e.g. AC
> > Initiated). I didn't know if an Array formula can search for multiple
> > criteria in the same row twice (once for all 2007 clients who were "Referred
> > to Tx" and then again for all who initiated tx (AC Initiated). I've tried
> > several variations of the formula below with no success. Any suggestions?
> > Thank you in advance, Dan
> >
> > The Formula I was using that isn't working is:
> >
> > =SUM((--(YEAR(Z4:Z3500)=2007))*(AN4:AN3500="AC
> > Initiated")*(AN4:AN3500="Referred to Tx"))

On Sun, 1 Jul 2007 09:02:04 -0700, Dan the Man
<(E-Mail Removed)> wrote:

>Thank you Max. The sumproduct alternative also works with "normal entry" as
>you suggested. Sorry for the multiple posts, but I'm a "newbie" to using this
>wonderful discussion group.
>
>Got another question to throw out at you or Ron (both who offered workable
>suggestions with normal entry or Array's).
>
>I attempted to modify another formula (using the "normal entry" suggestion
>from Max)without success. This formula has multiple search criteria (4). Here
>is that formula:
>
>=SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(S4:S3500,{"RF Hearing &
>Red. Fee","MR Hearing & Red. Fee","Red Flag Hearing Client";"Medical Risk
>Hearing"},0)))
>
>Thank you in advance!
>
>Dan
>


Try this **array-entered**


=SUM((YEAR(Z4:Z3500)=2007)*(S4:S3500={"RF Hearing & Red. Fee","MR Hearing &
Red. Fee","Red Flag Hearing Client","Medical Risk Hearing"}))



--ron

And the "array" response works.............Now for the "non array" response
from Max.....(lol). Best, Dan

"Ron Rosenfeld" wrote:

> On Sun, 1 Jul 2007 09:02:04 -0700, Dan the Man
> <(E-Mail Removed)> wrote:
>
> >Thank you Max. The sumproduct alternative also works with "normal entry" as
> >you suggested. Sorry for the multiple posts, but I'm a "newbie" to using this
> >wonderful discussion group.
> >
> >Got another question to throw out at you or Ron (both who offered workable
> >suggestions with normal entry or Array's).
> >
> >I attempted to modify another formula (using the "normal entry" suggestion
> >from Max)without success. This formula has multiple search criteria (4). Here
> >is that formula:
> >
> >=SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(S4:S3500,{"RF Hearing &
> >Red. Fee","MR Hearing & Red. Fee","Red Flag Hearing Client";"Medical Risk
> >Hearing"},0)))
> >
> >Thank you in advance!
> >
> >Dan
> >
>
>
> Try this **array-entered**
>
>
> =SUM((YEAR(Z4:Z3500)=2007)*(S4:S3500={"RF Hearing & Red. Fee","MR Hearing &
> Red. Fee","Red Flag Hearing Client","Medical Risk Hearing"}))
>
>
>
> --ron
>

The prob was you had a mixture of commas and semicolons separators in your
earlier:
> =SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(S4:S3500,{"RF Hearing &
Red. Fee","MR Hearing & Red. Fee","Red Flag Hearing Client";"Medical Risk
Hearing"},0)))

Just correct all the "comma" separators between the various text phrases
within that array to semicolons (, viz this rendition should work fine:
=SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(S4:S3500,{"RF Hearing &
Red. Fee";"MR Hearing & Red. Fee";"Red Flag Hearing Client";"Medical Risk
Hearing"},0)))

If you had all your text phrases listed in say, AX1 down,
you could also use either this simpler entire cols version (the "AX:AX" part)
(but it'll take a while to recalc):
=SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(S4:S3500,AX:AX,0)))

Or, this much faster, defined range version (the "AX$1:AX$10" part):
=SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(S4:S3500,AX$1:AX$10,0)))
where the text phrases would be listed within AX1:AX10
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dan the Man" wrote:
> .. Now for the "non array" response from Max .....

Max and Ron are great..............Dan

"Max" wrote:

> The prob was you had a mixture of commas and semicolons separators in your
> earlier:
> > =SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(S4:S3500,{"RF Hearing &
> Red. Fee","MR Hearing & Red. Fee","Red Flag Hearing Client";"Medical Risk
> Hearing"},0)))
>
> Just correct all the "comma" separators between the various text phrases
> within that array to semicolons (, viz this rendition should work fine:
> =SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(S4:S3500,{"RF Hearing &
> Red. Fee";"MR Hearing & Red. Fee";"Red Flag Hearing Client";"Medical Risk
> Hearing"},0)))
>
> If you had all your text phrases listed in say, AX1 down,
> you could also use either this simpler entire cols version (the "AX:AX" part)
> (but it'll take a while to recalc):
> =SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(S4:S3500,AX:AX,0)))
>
> Or, this much faster, defined range version (the "AX$1:AX$10" part):
> =SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(S4:S3500,AX$1:AX$10,0)))
> where the text phrases would be listed within AX1:AX10
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Dan the Man" wrote:
> > .. Now for the "non array" response from Max .....

welcome, Dan.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dan the Man" <(E-Mail Removed)> wrote in message
news:B9BF7EEF-BBE7-4CF3-9180-(E-Mail Removed)...
> Max and Ron are great..............Dan