SUMPRODUCT ON DATES

Iain Halder · 01-12-2005, 08:06 AM

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="Admitted
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'<

RagDyeR · 01-12-2005, 09:06 AM

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" <[email protected]> wrote in message
news:[email protected]...
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'<

Bob Phillips · 01-12-2005, 10:06 AM

Iain,

Not sure this is all too clear.

Do you mean that you have dates across columns and you need to count the
incidences in those columns, across worksheets, or just down the rows.

And by new data, do you mean it extends 1406:1499, or the columns?

Can you give some sample data?

--

HTH

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

"Iain Halder" <[email protected]> wrote in message
news:[email protected]...
> 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'<

Iain Halder · 01-12-2005, 02:06 PM

The dates are in one column only A01:A100, say ...

I need to know how many occurences of any other items in any other
column occured over 01:100 between given dates.

A B C
DATE SOURCE MOVEMENT
23/05/04 RATU Admitted to Unit
30/06/05 A&E Discharged

There are around 2000 rows of data and growing ....

* How many admissions came into the Unit via RATU between startdated
and enddate?

* How many admissions came into the Unit via A&E between startdated
and enddate?

These figures I obtain by manually typing in the starting row of a
start date and the ending row of the end date. I want to be able to
automate the process by getting the worksheet to use dates directly.

On Wed, 12 Jan 2005 13:54:22 -0000, "Bob Phillips"
<[email protected]> wrote:

>Iain,
>
>Not sure this is all too clear.
>
>Do you mean that you have dates across columns and you need to count the
>incidences in those columns, across worksheets, or just down the rows.
>
>And by new data, do you mean it extends 1406:1499, or the columns?
>
>Can you give some sample data?

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

RagDyeR · 01-12-2005, 07:06 PM

Have you tried the formula that I suggested?

Substitute your cell references and it should give you total combined
admissions for the dates you specify, which is what the formula you posted
did (added them together).

To break out the admissions separately between "RATU" and "A&E", just make 2
separate formulas:

=SUMPRODUCT((DATA!A10:A3000>=A1)*(DATA!A10:A3000<=A2)*(DATA!N10:N3000="RATU"
)*(DATA!S10:S3000="AdmittedtoUnit"))

=SUMPRODUCT((DATA!A10:A3000>=A1)*(DATA!A10:A3000<=A2)*(DATA!N10:N3000="A&E")
*(DATA!S10:S3000="AdmittedtoUnit"))

--

HTH,

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

"Iain Halder" <[email protected]> wrote in message
news:[email protected]...
The dates are in one column only A01:A100, say ...

I need to know how many occurences of any other items in any other
column occured over 01:100 between given dates.

A B C
DATE SOURCE MOVEMENT
23/05/04 RATU Admitted to Unit
30/06/05 A&E Discharged

There are around 2000 rows of data and growing ....

* How many admissions came into the Unit via RATU between startdated
and enddate?

* How many admissions came into the Unit via A&E between startdated
and enddate?

These figures I obtain by manually typing in the starting row of a
start date and the ending row of the end date. I want to be able to
automate the process by getting the worksheet to use dates directly.

On Wed, 12 Jan 2005 13:54:22 -0000, "Bob Phillips"
<[email protected]> wrote:

>Iain,
>
>Not sure this is all too clear.
>
>Do you mean that you have dates across columns and you need to count the
>incidences in those columns, across worksheets, or just down the rows.
>
>And by new data, do you mean it extends 1406:1499, or the columns?
>
>Can you give some sample data?

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

Iain Halder · 01-13-2005, 09:06 AM

Hi,

Your solution of obtaining the dates worked very well on my worksheet!

Thank you for your help there, I appreciate it!!!

Iain Halder

On Wed, 12 Jan 2005 04:52:51 -0800, "RagDyeR" <[email protected]>
wrote:

>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"))

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

RagDyeR · 01-13-2005, 01:06 PM

Thanks for the feed-back.

--

Regards,

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

"Iain Halder" <[email protected]> wrote in message
news:[email protected]...
Hi,

Your solution of obtaining the dates worked very well on my worksheet!

Thank you for your help there, I appreciate it!!!

Iain Halder

On Wed, 12 Jan 2005 04:52:51 -0800, "RagDyeR" <[email protected]>
wrote:

>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"))

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