Formula Problem. Anyone up for the challenge, lol?

[Dan the Man]

I need a formula which looks at the following factors: (Column U) clients who
showed program improvement, (Column F) clients who were mandated to
treatemnt, (Column F) clients who had a mandated evaluaiton, and (Column Y)
clients who completed treatment. Please note that Column F has a drop down
menu with three options: mand, vol, Eval-Mand. The formula I have below
renders the outcome to be "0", so I know I ham missing something.:

=SUMPRODUCT(--('Raw Data'!$U$4:$U$5000>0),--('Raw
Data'!$F$4:$F$5000="Mand"),--('Raw Data'!$F$4:$F$5000="Eval-Mand")*('Raw
Data'!$Y$4:$Y$5000="completed")


It may also be helpful to know that I originally had the formula only
looking at improvement for those who were mandated to treatment, and
completted, however I need to add in the additonal factor of those who were
mandated to have an evaluation. Again, both mandated to treatment, and
mandated to have an evaluation are "drop down" menu items in Column F. Any
help would be greatly appreciated.

Thanks in advance!

Dan

 

[Rick Rothstein]

On a quick look... that last term is being multiplied by the SUMPRODUCT...
it is not part of it, so the array range it contains is not being evaluated
by it. I think you may want this...

=SUMPRODUCT(--('Raw Data'!$U$4:$U$5000>0),--('Raw
Data'!$F$4:$F$5000="Mand"),--('Raw Data'!$F$4:$F$5000="Eval-Mand"),--('Raw
Data'!$Y$4:$Y$5000="completed"))

 

[JMB]

Your problem is you have two *and* conditions for Column F. Column F cannot
be "Mand" and "Eval-Mand" at the same time. I assume you wanted an *or*
condition for column F? Col U has to be > 0, Col F has to be either Mand or
Eval-Mand, and Col Y is "completed"?

If so, try:
=SUMPRODUCT(--('Raw Data'!$U$4:$U$5000>0),--(('Raw
Data'!$F$4:$F$5000="Mand")+('Raw Data'!$F$4:$F$5000="Eval-Mand")>0),--('Raw
Data'!$Y$4:$Y$5000="completed"))

Which could also be expressed
=SUMPRODUCT(('Raw Data'!$U$4:$U$5000>0)*('Raw
Data'!$F$4:$F$5000={"Mand","Eval-Mand"})*('Raw
Data'!$Y$4:$Y$5000="completed"))

 

[Dan the Man]

Hey Rick, thank you for your suggestion. I forgot to mention that the
"Eval-Mand" clients are not tracked for improvement (only those identified as
"Mand" are). Sorry about forgetting that important piece.

Thus the array should be evaluating:

(a) Mandated clients who completed treatment and showed improvement, and

(b) Evaluation mandated clients who merely completed treatment.

When checking the math by hand I know that I have 222 mandated clients who
completed treatment and showed improvement, and 64 evaluation mandated
clients who completed treatment. Thus, my formula should result with a raw
score of 286. Again, I apologize for leaving that piece of critical
information out. I'm a bit of a "space case" today............Dan

 

[JMB]

based on your follow up to Rick - do you want "Mand" clients who showed
improvement and completed the program plus "Eval-Mand" clients who completed
the program? You could try:

=SUMPRODUCT(--('Raw Data'!$U$4:$U$5000>0),--('Raw
Data'!$F$4:$F$5000="Mand"),--('Raw
Data'!$Y$4:$Y$5000="completed"))+SUMPRODUCT(--('Raw
Data'!$F$4:$F$5000="Eval-Mand"),--('Raw Data'!$Y$4:$Y$5000="completed"))

although there may be a better or shorter way to do it

 

[Dan the Man]

Just got home. Thanks for all the help everyone...................I
appreciate having this group.............You all have saved my life more than
once.............Dan