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.

=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.
>

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.
>

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.
> >