Iain,

Good detail, you are hearing us :-).

Although you don't state where the problem lays, dates or times, I think the
way that you have tested times is incorrect. Instead of {"02:00:00"},
try --"02:00:00", e.g.

b10:
=SUMPRODUCT((DATA!A2:A2000=B42)*(DATA!A2:A2000<=C 42)*(DATA!F2:F2000<(--"02:
00:00")))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Iain Halder" wrote in message
...
Hi,

I'm with the NHS and doing a worksheet which looks at various aspects
of admission and referrals to an A&E Department. I'm using SUMPRODUCT
as an Excel method for calculating.

However, I'm getting a strange aberration on some of the time-based
numbers. I've had good, no excellent, advice here in the past for my
little Excel problems and hope somebody can help here again too.

Part of what this worksheet is for is to calculate waiting times from
entering the main hospital to being seen and discharged. The
Government says we must complete everything within 4 hours. This
worksheet shows us how many patients we are processing and within what
time.

The calculation is laid out as follows ...

WAITING TIMES
Within 2 Hrs

b10=sumproduct((data!a2:a2000=b42)*(data!a2:a2000 <=c42)*(data!f2:f2000<{"02
:00:00"}))
Between 2 to 4 Hrs

b11=sumproduct((data!a2:a2000=b42)*(data!a2:a2000 <=c42)*(data!f2:f2000<={"0
2:00:00"}))-b10
After 4 Hrs

b12=sumproduct((data!a2:a2000=b42)*(data!a2:a2000 <=c42)*(data!f2:f2000{"04
:00:00"}))
Not Recorded

b13=sumproduct((data!a2:a2000=b42)*(data!a2:a2000 <=c42)*(data!f2:f2000={""}
))


data! = Raw Data Sheet
a2:a2000 = List of dates in the form "28-Dec-04"
f2:f2000 = Times in format 00:00:00
b42 & c42 = From & To Dates

There are 55 patients in this week.
b10 resolves to 55 (wrong should be 24)
b11 resolves to <BLANK! (wrong should be 9)
b12 resolves to <BLANK! (wrong should be 3)
b13 resolves to 19 (correct)

B10, B11 & B12 are not resolving correctly at all. I'm assuming
(though may be wrong) that the error is in the latter part around the
< = and time area but have not been able to resolve my mistake.

Can anybody please tell me what I am doing wrong here?

Iain Halder
Rescued Cats & Kittens Needing Homes
'o'< www.celiahammond.org 'o'<
'o'< www.cat77.org.uk 'o'<