Calculating data within a date range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

KeK23 - 26 Sep 2007 12:16 GMT

Does anyone have any ideas on how to calculate data that is listed by date
(not necessarily in date order. I am trying to come up with a week ending
subtotal in a summary sheet from a number of different worksheets. My data
is listed as shown in each worksheet. If anyone could offer a formula to
put in my summary sheet it would be greatly appreciated.

Column b Column I
Date Net weight in tonnes
3/09/2007 37.5

Reply to this Message

Mike H - 26 Sep 2007 12:38 GMT

Hi,

Dates in Column A and what to sum in column B.
This sums data between 2 dates in C1 & C2. Adapt the ranges to suit:-

=SUMPRODUCT((A1:A4>C1)*(B1:B4))-SUMPRODUCT((A1:A4>C2)*(B1:B4))

> Does anyone have any ideas on how to calculate data that is listed by date
> (not necessarily in date order. I am trying to come up with a week ending
[quoted text clipped - 5 lines]
> Date Net weight in tonnes
> 3/09/2007 37.5

Reply to this Message

KeK23 - 26 Sep 2007 13:00 GMT

Mike,

Thanks for the help the formula worked but gave me a huge negative number,
which was unexpected do I need to have 2 date columns in my worksheet. I
took the dates for C1 and C2 from the column headings in my summary sheet.
Thanks again

> Hi,
>
[quoted text clipped - 12 lines]
> > Date Net weight in tonnes
> > 3/09/2007 37.5

Reply to this Message

Mike H - 26 Sep 2007 13:10 GMT

As long as C1, C2 and column A are correctly formatted dates then it should
work.

Mike

> Mike,
>
[quoted text clipped - 19 lines]
> > > Date Net weight in tonnes
> > > 3/09/2007 37.5

Reply to this Message

KeK23 - 26 Sep 2007 13:30 GMT

Mike,

Thanks got it to work, but have another question. I have tried to copy this
and paste formula for each consecutive week ending but am getting VALUE
appear. Any ideas.

Thanks

> As long as C1, C2 and column A are correctly formatted dates then it should
> work.
[quoted text clipped - 24 lines]
> > > > Date Net weight in tonnes
> > > > 3/09/2007 37.5

Reply to this Message