Can I use an array formula with multiple criteria in the same row?

[Dan the Man]

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"))

 

[Max]

Try this sumproduct alternative, normal ENTER will do:
=SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(AN4:AN3500,{"AC
Initiated";"Referred to Tx"},0)))

 

[T. Valko]

Try this (normally entered):

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

Biff

 

[Dan the Man]

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

 

[Ron Rosenfeld]

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

 

[Dan the Man]

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

 

[Max]

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

 

[Dan the Man]

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

The prob was you had a mixture of commas and semicolons separators in your
earlier:
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]

welcome, Dan.