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

D

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

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

T. Valko

Try this (normally entered):

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

Biff
 
D

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
 
R

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
 
D

Dan the Man

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

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
 
D

Dan the Man

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

Max said:
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
 

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