FORMULA I NEED HELP WITH:

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

MY PROBLEM

In Row Z I am listing dates, and in Row AN I am listing whether or not
clients were Referred to Treatment (Tx), and whether or not they began
treatment (e.g. AC Initiated). I can get the above formula to work with only
one search criteria in Row AN and the appliable date range in Row Z (e.g. the
year 2007), however I cannot get the formula to work, when I enter 2 search
criteria in Row AN (Referred to Tx and AC Initiated). Can an array formula
search the same row twice (AN), and count all clients who were seen in 2007
who were: (a) Referrred for Tx and/or (b) Initiated Tx (eg. AC Initiated).

Any help would be much apreciated!

Dan

try sumproduct() with the same criteria
or try enetering your formula as an array formula control-shift-enter

"Dan the Man" wrote:

> FORMULA I NEED HELP WITH:
>
> =SUM((--(YEAR(Z4:Z3500)=2007))*(AN4:AN3500="Referred to Tx))*(AN4:AN3500="AC
> Initiated"))
>
> MY PROBLEM
>
> In Row Z I am listing dates, and in Row AN I am listing whether or not
> clients were Referred to Treatment (Tx), and whether or not they began
> treatment (e.g. AC Initiated). I can get the above formula to work with only
> one search criteria in Row AN and the appliable date range in Row Z (e.g. the
> year 2007), however I cannot get the formula to work, when I enter 2 search
> criteria in Row AN (Referred to Tx and AC Initiated). Can an array formula
> search the same row twice (AN), and count all clients who were seen in 2007
> who were: (a) Referrred for Tx and/or (b) Initiated Tx (eg. AC Initiated).
>
> Any help would be much apreciated!
>
> Dan

On Sat, 30 Jun 2007 16:12:00 -0700, Dan the Man
<(E-Mail Removed)> wrote:

>FORMULA I NEED HELP WITH:
>
>=SUM((--(YEAR(Z4:Z3500)=2007))*(AN4:AN3500="Referred to Tx))*(AN4:AN3500="AC
>Initiated"))
>
>MY PROBLEM
>
>In Row Z I am listing dates, and in Row AN I am listing whether or not
>clients were Referred to Treatment (Tx), and whether or not they began
>treatment (e.g. AC Initiated). I can get the above formula to work with only
>one search criteria in Row AN and the appliable date range in Row Z (e.g. the
>year 2007), however I cannot get the formula to work, when I enter 2 search
>criteria in Row AN (Referred to Tx and AC Initiated). Can an array formula
>search the same row twice (AN), and count all clients who were seen in 2007
>who were: (a) Referrred for Tx and/or (b) Initiated Tx (eg. AC Initiated).
>
>Any help would be much apreciated!
>
>Dan

The multiplication operator mimics AND.

To mimic OR you need the addition operator.

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

or, perhaps using an array constant:

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

or, since I think the double unary is unneccessary:

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


--ron

Why repeat your posting? Don't you read the responses to your earlier
posting?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"Ron Rosenfeld" wrote:

> On Sat, 30 Jun 2007 16:12:00 -0700, Dan the Man
> <(E-Mail Removed)> wrote:
>
> >FORMULA I NEED HELP WITH:
> >
> >=SUM((--(YEAR(Z4:Z3500)=2007))*(AN4:AN3500="Referred to Tx))*(AN4:AN3500="AC
> >Initiated"))
> >
> >MY PROBLEM
> >
> >In Row Z I am listing dates, and in Row AN I am listing whether or not
> >clients were Referred to Treatment (Tx), and whether or not they began
> >treatment (e.g. AC Initiated). I can get the above formula to work with only
> >one search criteria in Row AN and the appliable date range in Row Z (e.g. the
> >year 2007), however I cannot get the formula to work, when I enter 2 search
> >criteria in Row AN (Referred to Tx and AC Initiated). Can an array formula
> >search the same row twice (AN), and count all clients who were seen in 2007
> >who were: (a) Referrred for Tx and/or (b) Initiated Tx (eg. AC Initiated).
> >
> >Any help would be much apreciated!
> >
> >Dan
>
> The multiplication operator mimics AND.
>
> To mimic OR you need the addition operator.
>
> =SUM((--(YEAR(Z4:Z3500)=2007))*((AN4:AN3500="Referred to Tx"))+(AN4:AN3500="AC
> Initiated"))))
>
> or, perhaps using an array constant:
>
> =SUM((--(YEAR(Z4:Z3500)=2007))*(AN4:AN3500={"Referred to Tx","AC Initiated"}))
>
> or, since I think the double unary is unneccessary:
>
> =SUM((YEAR(Z4:Z3500)=2007)*(AN4:AN3500={"Referred to Tx","AC Initiated"}))
>
>
> --ron
>

Thank you very much Ron! You soloved my problem, and you were correct, the
double unary is indeed unneccessary!

Happy 4th of July!

Dan

"Ron Rosenfeld" wrote:

> On Sat, 30 Jun 2007 16:12:00 -0700, Dan the Man
> <(E-Mail Removed)> wrote:
>
> >FORMULA I NEED HELP WITH:
> >
> >=SUM((--(YEAR(Z4:Z3500)=2007))*(AN4:AN3500="Referred to Tx))*(AN4:AN3500="AC
> >Initiated"))
> >
> >MY PROBLEM
> >
> >In Row Z I am listing dates, and in Row AN I am listing whether or not
> >clients were Referred to Treatment (Tx), and whether or not they began
> >treatment (e.g. AC Initiated). I can get the above formula to work with only
> >one search criteria in Row AN and the appliable date range in Row Z (e.g. the
> >year 2007), however I cannot get the formula to work, when I enter 2 search
> >criteria in Row AN (Referred to Tx and AC Initiated). Can an array formula
> >search the same row twice (AN), and count all clients who were seen in 2007
> >who were: (a) Referrred for Tx and/or (b) Initiated Tx (eg. AC Initiated).
> >
> >Any help would be much apreciated!
> >
> >Dan
>
> The multiplication operator mimics AND.
>
> To mimic OR you need the addition operator.
>
> =SUM((--(YEAR(Z4:Z3500)=2007))*((AN4:AN3500="Referred to Tx"))+(AN4:AN3500="AC
> Initiated"))))
>
> or, perhaps using an array constant:
>
> =SUM((--(YEAR(Z4:Z3500)=2007))*(AN4:AN3500={"Referred to Tx","AC Initiated"}))
>
> or, since I think the double unary is unneccessary:
>
> =SUM((YEAR(Z4:Z3500)=2007)*(AN4:AN3500={"Referred to Tx","AC Initiated"}))
>
>
> --ron
>

On Sun, 1 Jul 2007 08:40:01 -0700, Dan the Man
<(E-Mail Removed)> wrote:

>Thank you very much Ron! You soloved my problem, and you were correct, the
>double unary is indeed unneccessary!
>
>Happy 4th of July!
>
>Dan

You're welcome. Glad to help. Thanks for the feedback.
--ron