If I understand what you're looking to do,

Say your dates are in Column B, and the entire data list is in rows 10 to
3000.

Enter you're starting date to look up in A1, and you're ending date in A2.

Then try this:

=SUMPRODUCT((DATA!B10:B3000=A1)*(DATA!B10:B3000<= A2)*(DATA!N10:N3000={"A&E"
,"RATU"})*(DATA!S10:S3000="AdmittedtoUnit"))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


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

I have a worksheet where I need to be able to calculate various totals
but initially based on dates firstly just by the month on e one sheet
(ie Jan, Feb, Mar, etc) and secondly by week on another sheet (ie 1st
Jan to 7th Jan, 8th Jan to 14th Jan, etc) through the entire year in 7
day increments.

Below is an example of what I am doing using SUMPRODUCT.

=(SUMPRODUCT(--(DATA!N1406:N1499="A&E"),--(DATA!S1406:S1499="Admitted
to
Unit")))+(SUMPRODUCT(--(DATA!N1406:N1499="RATU"),--(DATA!S1406:S1499="Admitt
ed
to Unit")))

The numbers 1406:1499 constantly repeated for various columns actually
represents those parts of the worksheet which correspond to between
dates.

The reality is that whenever I want a total I have to look at the raw
data sheet and manually count the rows between the dates I want and
then manually insert these numbers into the final worksheets.
Sometimes new data appears from weeks ago which then has to be
included. So I re-count manually for that week and then have to
recount for all the intervening weeks from then to the present. As you
can imagine it gets very tedious.

I need a way of doing the above sumproduct'ing but just have the
ability to insert from and to dates instead. this way the worksheet is
more automated (and accurate).

Hope I have explained this OK.

Thanks in advance!

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