Prev: how do i calculate a %
Next: input time by keybrd in locked worksheet
From: Toll Jenny Toll on 15 Feb 2010 22:40
I would like to save myself a whole lot of work please...
I have two spreadsheets, one has all the days of the year across the
columns, and the next has all the week ending dates across the colums.
What I need to do is sum the data in each of the rows below the daily dates
into weekly chunks on the same rows in the Weekly spreadsheet.
I have also added these up into montly chunks, but there was only 12 sums to
do so wasn't too bad. I don't relish having to do 52 of them.
Any assistance would be appreciated.

From: T. Valko on 15 Feb 2010 23:41
If you have *all* the dates for a year across a row then you must be using
Excel 2007. (prior versions only have 256 columns, not enough for a year's
worth of dates)

Try this...

B1:NB1 = dates from 1/1/2010 to 12/31/2010
B2:NB2 = values to sum

I'm assuming you want to sum from Monday to Sunday?

B10:??10 = week ending dates for the year (Sunday dates)

Enter this formula in B11:

=SUM(B2:INDEX(2:2,MATCH(B10,1:1,0)))

Enter this formula in C11:

=SUMIFS(2:2,1:1,">"&B10,1:1,"<="&C10)

Copy across as needed.

--
Biff
Microsoft Excel MVP


"Toll Jenny" <Toll Jenny(a)discussions.microsoft.com> wrote in message
news:26A4085A-21EA-435B-BAFE-24083F7E866C(a)microsoft.com...
>I would like to save myself a whole lot of work please...
> I have two spreadsheets, one has all the days of the year across the
> columns, and the next has all the week ending dates across the colums.
> What I need to do is sum the data in each of the rows below the daily
> dates
> into weekly chunks on the same rows in the Weekly spreadsheet.
> I have also added these up into montly chunks, but there was only 12 sums
> to
> do so wasn't too bad. I don't relish having to do 52 of them.
> Any assistance would be appreciated.
>


 | 
Pages: 1
Prev: how do i calculate a %
Next: input time by keybrd in locked worksheet