Calculating data within a date range
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 |
Calculating data within a date range
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:A4C1)*(B1:B4))-SUMPRODUCT((A1:A4C2)*(B1:B4)) "KeK23" wrote: 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 |
Calculating data within a date range
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 "Mike H" wrote: 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:A4C1)*(B1:B4))-SUMPRODUCT((A1:A4C2)*(B1:B4)) "KeK23" wrote: 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 |
Calculating data within a date range
As long as C1, C2 and column A are correctly formatted dates then it should
work. Mike "KeK23" wrote: 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 "Mike H" wrote: 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:A4C1)*(B1:B4))-SUMPRODUCT((A1:A4C2)*(B1:B4)) "KeK23" wrote: 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 |
Calculating data within a date range
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 "Mike H" wrote: As long as C1, C2 and column A are correctly formatted dates then it should work. Mike "KeK23" wrote: 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 "Mike H" wrote: 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:A4C1)*(B1:B4))-SUMPRODUCT((A1:A4C2)*(B1:B4)) "KeK23" wrote: 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 |
All times are GMT +1. The time now is 08:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com