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(A N4: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"))

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(S 4: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(A N4: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
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
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(S 4: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(S 4: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(S 4:S3500,AX:AX,0)))

Or, this much faster, defined range version (the "AX$1:AX$10" part):
=SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(S 4: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 .....

Try this (normally entered):

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

Biff

"Dan the Man" <Dan the wrote in message
...
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"))