Sum Days into Weeks

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.

0
Utf
2/16/2010 3:40:05 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
2075 Views

Similar Articles

[PageSpeed] 51

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@discussions.microsoft.com> wrote in message 
news:26A4085A-21EA-435B-BAFE-24083F7E866C@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.
> 


0
T
2/16/2010 4:41:31 AM
Reply:

Similar Artilces:

Maximum number of cells for 'SUM' ?
I have a spreadsheet with 56,314 rows of text labels. I have a formula in each row off to the right of each text label. The formula tests for a specific text string contained in the label and records a 1 if true. At the top of the sheet above this column of formulas, I want to sum the number of 'TRUE' responses, or 1(s). My formula is: =SUM(I5:I56,314). But this returns a #NAME? error message. If I limit the SUM formula to not cover beyond cell I9168, then I get a correct answer and no error msg. Is this a limit of Excel's ability to calculate, a lim...

Fixing bills to appear on a set day
Hi, I have a few regular bills on direct debit which sometimes get paid on 31st of the month, and sometimes on 1st of the next month. This is really annoying when asking for reports. In some months I have no home insurance, for example, and in others I have a double charge. Although I've set a fixed monthly date in the "bills" section of money, when I do an online update with my bank (Nationwide) and I accept the match which money makes between the bank's data and my own, the entry ends up getting changed to the date payment actually left the bank, which varies as described...

Conditional Formulas/Formatting
Here's my problem, based on my example: I've got a checkmark in cell A1, which I refer to in the Post Ref Column by "=A1". If there's a checkmark in the Post Ref column, then the entry is a balance (Either Debit or Credit). There should ALWAYS be TWO and ONLY TWO entries. One under the Debit OR Credit Column next to Post Ref. The other under Debit OR Credit under Balance at Top Right of the example image below. If the Balance is a Debit, there should be two Debit column entries. Vice Versa if the Balance is a Credit. The next row down, if the Balance above is a...

Excel
I'm having a problem with adding a column of numbers. I locate the cell in the column that I want the total, hit AutoSum and the Enter and the answer in the cell is all #########. What am I doing wrong? Widen the column, the result of the sum is to wide to be viewed -- Regards, Peo Sjoblom "growtree" <growtree@discussions.microsoft.com> wrote in message news:28CF3625-1E85-422C-AC93-F1E07726FF2A@microsoft.com... > I'm having a problem with adding a column of numbers. I locate the cell > in > the column that I want the total, hit AutoSum and the En...

Sum in Opportunities
Our views in Opportunities display expected revenue from each Opp. Is there an easy way to sum the total and display the results in the views? thanks in advance, Thomas if you have multiple revenue fields on a form then you can use javascript to total them up nad place the result in another field then display that field on a view. ============================== John O'Donnell Microsoft CRM MVP http://www.crowecrm.com "chengthomas" <chengthomas@discussions.microsoft.com> wrote in message news:34B2615F-5B2C-47D6-A58C-2AB706D7CA0F@microsoft.com... > Our views in O...

CRM log file 14GB after 1 week use
Does anyone know why the CRM SQL log file would balloon to over 14GB after only 1 week of use? Is there a way to limit how big the log file grows? At this rate, my large server HD will be full in a month! Thanks, Josh Josh, You really need to make sure that you have backups scheduled for the DB's. If you aren't concerned about recovery, than you should change the recovery mode on the CRM DB's to Simple from Full. As for the reasons, what have you been doing during the week? Some processes do result in a lot of logging. Matt "Josh Gard" <josh@commdesign.net&g...

Last day for the early bird registration - Excel User Conference - Sept 16/17
Today, July 15th is the last day for early bird registration. Sign up today before the prices go up. You can sign up online and pay with PayPal or download the brochure. Get the brochure post marked today and get early bird pricing. Feel free to contact me if you have any questions. There is an email link on the web site. -- Damon Longworth Don't miss out on the 2005 Excel User Conference Sept 16th and 17th Stockyards Hotel - Ft. Worth, Texas www.ExcelUserConference.com ...

Ajax Developers' Day added to XTech 2006 agenda
Ajax Developers' Day added to XTech 2006 agenda XTech 2006 - 17-19 May - Hotel Grand Krasnopolsky - Amsterdam, The Netherlands An Ajax Developers' Day has been added to the schedule of events at XTech 2006. In response to the rapidly developing world of Ajax user interfaces on the browser, XTech has put together a day for Ajax developers to meet, discuss and learn. Who should attend - Web developers involved in or planning the creation of dynamic Ajax-based web user interfaces, or involved in the creation of Ajax toolkits. The preliminary schedule includes: 9:00 Keynote talk...

Sum a DLookup
I have a report with grouping based on RepName and it displays each reps totals in [TxtTotals]. On that report I have another text box [TxtRate] with =DLookUp("CommRate","TblCommissionRates",[TxtTotal] & " Between AmountCollectedLow And AmountCollectedHigh") This gets my CommRate for each rep from a (Unrelated table) and this works fine. But I'm trying to get the average rate in the report footer But when i use =Avg([TxtRate]) its looking for a parameter [TxtRate] How can I do this without changing my query? Thanks Arlend The aggregate function...

excel vba
is it possible to set something up so that you get eg. K19 being the su of B19*D19, however the sum value in K19 only shows up when there ar values in B19 and D19? I don't want to have =SUM(B19*D19) in the K1 cell and copy and paste the formula into the cells below it. If ther is no value in B19 and D19, it will produce a 0 in the cell. That' not what I am looking for. I want to have that formula ready to su whenever numbers are added into the B19 and D19 cells, and not have an values in K19 until the others have value -- Message posted from http://www.ExcelForum.com Chief, how ...

Difference between dates as xxYears yy Months zz days
I have a number of pairs of dates where I wish to show the difference between each pair in the format of e.g. 3 years 7 months 9 days. I can work out the yearsand months but don't know how to then get the number of remaining days allowing for differences in number of days in a month or year. Does anyone have a set of formulae or a macro that will produce what I want? Thanks Rich 80105 subtract one date from the other and format the result cell using custom format of yy "years" mm "months" dd "days" hope this help -- tony ----------------------------...

I have a live data feed that updates a cell all day. Can I graph?
I have an Excel sheet that updates live market prices all day. Is there anyway that I can keep track of all the changes to graph them, look at the high and low, etc...? A macro to copy the desired data to another sheet where it can be archived to use later, as desired. -- Don Guillett SalesAid Software donaldb@281.com "windman" <windman@discussions.microsoft.com> wrote in message news:DEF90951-DFC3-4ABB-8D63-8614FD0CE420@microsoft.com... > I have an Excel sheet that updates live market prices all day. Is there > anyway that I can keep track of all the changes to gr...

Exchange 2003: One user getting "No transport provider was available for delivery to this" several times a day
We run Exchange 2003 in a WIndows 2000 domain. Most of our clients (including the problem user) use citrix, so the setup for individuals has little variation on basics such as Outlook 2003. One user continually has problems sending out and several times a day (sometimes much more), she is prevented from sending emails with the error: The following recipient(s) could not be reached: John Doe on 20/09/2007 10:01 No transport provider was available for delivery to this recipient. I looked up the 'no transport' issue on Microsoft KB, and nothing was really applica...

Count-down timer? (e.g. 10 days left...9...8...)
Is there a way to program a count-down timer in Excel? For example, say I need to buy toner cartridges every 3 months, and I just bought some. Then I can reset the timer, and it'll indicate another 3 months. When the three months are up, another cell will turn go from "Toner in stock" to "Buy toner." Does anyone know how to program excel to do something like this? Thanks! I wish. all forms have a timer event but unfortunately it can be use only on 1 event. some of my users are buyers and they have requested this also but the most i can do is use the info the...

auto sum a group of numbers
I use Excel to figure interest earned each year on a number of funds for our church. I have devised a formala to calculate and divide the amount of interest earned in a given amount of days. However, when I use the autosum function, my total is off by one cent, being one cent too low. Thanks for any help you might b able to provide. 6/30/2003 6/30/2003 $$ DAYS 2003 INT ROUNDED 12/31/2003 INT ON as of 12/31/03 BALANCES THIS COL DAYS TO USE 185 INTEREST TO DIVIDE 53.52 GENERAL FUND 189.56 189.56 ...

Vlookup with sums
I have the following spreadsheet. 1609719837 M51410 10 Ser - 5/8" x 50 Ft. 2000 1609710593 2058100 5/8 X 100 Platinum 100 1609718669 10TNSGF 10 Ser - 1/2" x 50 Ft 1609719839 M51470 25 Ser - Industrial 3/4" x 75 Ft. 500 Column A is Product Number, Column B is a Description and Column C is the number of pieces we will be producing. I have a table that does a vlookup on the Product Number and returns a unit of measure which I multiple by the number of pieces we will be producing. My question is how can I have one formula at the bottom of column C th...

how to calculate 15 working days of a certaing record
VBA Master, i just want to ask on how to calculate only the 15 working days of a certaing record (dont mind the holidays only the saturdars and sundays will be disregard on count). hope you can help me. many thanks. pnexs ignus -- just need help One approach is outlined in http://www.mvps.org/access/datetime/date0012.htm at "The Access Web". I showed another way in my September, 2004 "Access Answers" column in Pinnacle Publication's "Smart Access". You can download the column (and sample database) for free at http://www.accessmvp.com/DJSteele/...

project server 2007
Hi there, I connect to project server 2007 with my ms project 200 pro with manually controlling connection state and during logging in I deselect "load summary resource assignments". Then I open my previously baselined project. I go to resource usage view to see my resources baselinework hours. I see them for each assignment but not in summary. The total baseline work is not summed up for each week for instance, this field is empty. I understand this resource might have other tasks on other projects that are not baselined yet but I open the project without these other ...

hour sum setup
i am currently trying to write an automated wage sheet for my temporary workers. i have finished the sheets and most of the formulas, however i am stuck. if the temporary worker works more than 39 hours per week thay go into time and a half, this is pretty straight forward, the problem is that if they work past 2 in the morning they get night rate, so the hours they work needs to be split up into the different columns. e.g. they worked: Monday - 8 hours Tuesday - 12 hours (at Night Shift rate) Wednesday - 8 hours Thursday - 8 hours Friday - 11.5 hours (at Night Shift rate) These hours would...

I am working on calendars, how do I change days of the week from .
It usually works best if you actually include a message. -- John Inzer return e-mail disabled ...

Find dates to calculate amount of work per day? Possible?
I've been reading the discussions trying to find an instance where someone has asked about this before and I can't seem to find anything. The sheet that I have has the date that the work was done and how many units were completed per map section. So there could be multiple rows with that date on it. What I'm trying to figure out is if there is a way to have Excel search for a date and then get all of the units done that day, even if they are on multiple rows. Then I can calculate how many units per day are being done. I don't know how to do VBA, so everything I'...

How can I autofill dates having a blank cell between each day?
Hi Dee, Enter a date in cell A1 (eg 5/13/08), enter the next day's date in cell A3 (eg 5/14/08). Select cells A1 trough to A4 (ie select two dates and two blank cells). See the little dot at the bottom right of the selection rectangle? Click on that (the cursor will change to a cross when you are over the right spot) and drag it down as far as you want. Ed Ferrero www.edferrero.com ...

Summing Subtotals
I have a worksheet with numerous subtotals and a grandtotal. The subtotals are separated by several individual entries for each subtotal. I have tried selecting each subtotal and the grand total cell, but when I hit enter it shows only the last subtotal. What am I doing wrong? Thanks for your help! -- jim Jim, To sum a column of a sheet with subtotals included, you can use =SUM(B:B)/2 OR =SUM(B:B)/3 Since you have the grandtotal showing... But you really shouldn't need to do that. Describe better what it is that you are looking to do, and we can come up with a better way - Piv...

Formulas - SUM
Hi, I'm wondering if there is a variation on the SUM formula to pick up data in rows/columns added outside the existing range? Thanks in advance to anyone who can help. -- Carla No. Regards, Fred "Carla" <Carla@discussions.microsoft.com> wrote in message news:EB798A82-BE94-40A7-A2FF-B0EDA5634751@microsoft.com... > Hi, > > I'm wondering if there is a variation on the SUM formula to pick up data > in > rows/columns added outside the existing range? > > Thanks in advance to anyone who can help. > > -- > Carl...

sum cells
I am trying to total employee schedule so some cell have D/O day off, R/O requested off, so when I try to total all associates by the day I get #VALUE! in cell and total cell. Is there a way to total only numbers and ignore #VALUE! Try this: =SUMIF(A1:A10,"<>#VALUE!") Why not fix the formula that causes the error in the first place? What is the formula that does this: >when I try to total all associates by the day I get #VALUE! Biff "SteveD" <SteveD@discussions.microsoft.com> wrote in message news:7D8BF85A-F76E-4F35-95AC-50B77B128304@microsoft.com....