I can't get my formula to do what I want it to do..........HELP!

[Dan the Man]

Column E Column H Column Y
Program # of Repeat Svs Completion Status

IOP 2 Completed
OP 1 ASA
CIC 0 Admin Disc
TP 4 Completed
IOP 3 Completed
IOP 2 ASA

In the above example, I want to look at home many times an individual in a
particular program (Column E), has returned for services (Column H). I am
also looking at the individuals completion status (Column Y). Using the
example above, I should be obtaining the following outcomes:

I am also looking at particular dates of service (which the formula below
includes). Unforuntaely, the formula I have written doesn't accurately
reflect the column H information I desire. The current formula only tells me
how many occurances greater than 0, and indivdual has had repeat services. In
order words, it would tell me that In our IOP Program, (3) people had repeat
services (only counting the "occurance" of repeat services, and not the
actual "summing" of the number of times an individual has returned for
services.

I had also not included the Column Y parameter which looks at the
individuals "completion" status, and was having difficulty encorporating that
informtion into my formaula............Any help would be appreciated. The
formula below is what I currently have, but obviously I need to modify and
expand this formula in order to get what I want.....................MUCH
THANKS in Advance, Dan

=SUMPRODUCT(--('Raw Data'!$X$4:$X$5000>=DATE(2008,4,1)),--('Raw
Data'!$X$4:$X$5000<=DATE(2008,6,30)),--('Raw Data'!$H$4:$H$5000>0),--('Raw
Data'!$E$4:$E$5000="IOP"))

 

[Dave Peterson]

In this formula:

=SUMPRODUCT(--('Raw Data'!$X$4:$X$5000>=DATE(2008,4,1)),
--('Raw Data'!$X$4:$X$5000<=DATE(2008,6,30)),
--('Raw Data'!$H$4:$H$5000>0),
--('Raw Data'!$E$4:$E$5000="IOP"))

You're counting the number of rows where all these things are true at the same
time.

If you just want to sum column H, you could use:

=SUMPRODUCT(--('Raw Data'!$X$4:$X$5000>=DATE(2008,4,1)),
--('Raw Data'!$X$4:$X$5000<=DATE(2008,6,30)),
('Raw Data'!$H$4:$H$5000),
--('Raw Data'!$E$4:$E$5000="IOP"))

The -- converts true/false's to 1/0's. I don't need to do that conversion for
column H. They're already numbers.

But this will sum everything in column H. Both 0's (which wouldn't matter) and
negatives (which couldn't happen in your data).

But if you're doing this where those values could be negative, you could use:

=SUMPRODUCT(--('Raw Data'!$X$4:$X$5000>=DATE(2008,4,1)),
--('Raw Data'!$X$4:$X$5000<=DATE(2008,6,30)),
--('Raw Data'!$H$4:$H$5000>0),
--('Raw Data'!$E$4:$E$5000="IOP"),
('Raw Data'!$H$4:$H$5000))

This still does the check for positive, but adds the values when all those
checks are true.

 

[Sheeloo]

You just need to add to the formula to get the total no. of service calls
('Raw Data'!$H$4:$H$5000)

You can add other components on similar lines. You need to remember that if
you have a condition in any component it will return 1 and 0 which gives you
the count. If you want the sum of acutal values use them without any
condition...

There are innumerable posts on sumproduct... if this does not answer your
question then search through them or add to this post again.

 

[Dan the Man]

Thanks Dave. I learn something every day. And you are correct, that my data
for column h would never produce negatives, so the first formula alteration
you made was the answer.

Have a great weekend!

Dan