Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing Weekly Totals into Monthly Totals
06/01/06 06/08/06 06/15/06 06/22/06 06/29/06
fish 10 12 11 14 12 meat 40 45 41 41 43 eggs 50 50 51 52 53 fruit 1 2 1 3 2 veggies 90 91 92 89 89 I need a way to summarize this weekly data into montly data. For example June Fish totals would be 59. I am going to have 3-4 yrs of weekly data that I need to show by month. I know I can chart it, but I want it in data sheet format not charts. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing Weekly Totals into Monthly Totals
=SUMPRODUCT((MONTH($B$1:$R$1)=6)*(B2:R2))
"steph44haf" skrev: 06/01/06 06/08/06 06/15/06 06/22/06 06/29/06 fish 10 12 11 14 12 meat 40 45 41 41 43 eggs 50 50 51 52 53 fruit 1 2 1 3 2 veggies 90 91 92 89 89 I need a way to summarize this weekly data into montly data. For example June Fish totals would be 59. I am going to have 3-4 yrs of weekly data that I need to show by month. I know I can chart it, but I want it in data sheet format not charts. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing Weekly Totals into Monthly Totals
Personally I would first normalise your data table using the following tip
from John Walkenbach (Should take no more than 2 minutes):- http://j-walk.com/ss/excel/usertips/tip068.htm and then just throw a pivot table at it and that gives you the option to group dates by month automatically. You will find you can do far more with your data when normalised as opposed to a table such as the one you have there -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------- It's easier to beg forgiveness than ask permission :-) "steph44haf" wrote: 06/01/06 06/08/06 06/15/06 06/22/06 06/29/06 fish 10 12 11 14 12 meat 40 45 41 41 43 eggs 50 50 51 52 53 fruit 1 2 1 3 2 veggies 90 91 92 89 89 I need a way to summarize this weekly data into montly data. For example June Fish totals would be 59. I am going to have 3-4 yrs of weekly data that I need to show by month. I know I can chart it, but I want it in data sheet format not charts. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing Weekly Totals into Monthly Totals
How do I normalize? The tip below talks about a reverse database table...
"Ken Wright" wrote: Personally I would first normalise your data table using the following tip from John Walkenbach (Should take no more than 2 minutes):- http://j-walk.com/ss/excel/usertips/tip068.htm and then just throw a pivot table at it and that gives you the option to group dates by month automatically. You will find you can do far more with your data when normalised as opposed to a table such as the one you have there -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------- It's easier to beg forgiveness than ask permission :-) "steph44haf" wrote: 06/01/06 06/08/06 06/15/06 06/22/06 06/29/06 fish 10 12 11 14 12 meat 40 45 41 41 43 eggs 50 50 51 52 53 fruit 1 2 1 3 2 veggies 90 91 92 89 89 I need a way to summarize this weekly data into montly data. For example June Fish totals would be 59. I am going to have 3-4 yrs of weekly data that I need to show by month. I know I can chart it, but I want it in data sheet format not charts. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Template to track weekly & monthly totals, by day & date? | Excel Discussion (Misc queries) | |||
Template track phone traffic to determine weekly, monthly trends. | New Users to Excel | |||
Collecting weekly and monthly totals from daily data | Excel Worksheet Functions | |||
Weekly and Monthly Subtotals | Excel Discussion (Misc queries) | |||
How do I sum YTD totals based on monthly totals | Excel Discussion (Misc queries) |