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

"JMB" wrote:

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$50000),--('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
"JMB" wrote:

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$50000),--(('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$50000)*('Raw
Data'!$F$4:$F$5000={"Mand","Eval-Mand"})*('Raw
Data'!$Y$4:$Y$5000="completed"))


"Dan the Man" wrote:

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$50000),--('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