How to sum up the value in column based on a 5 day week period?

Hello,
Im trying to add the amount of time spent on a type of machinery based on a  
5 day week period. For example from 3/1/2010 to 3/5/2010 not including 
weekends.
On the sheet with the data of the amount of time I need added is layed out 
like this:
             Colomn A          Colomn B           Colomn K
Example:3/1/2010              PL 1                    1:54 
             3/1/2010              PL 5                    0:13
             3/1/2010              PL 1                    0:07
             3/2/2010              PL 5                    0:13
             3/2/2010              PL 1                    1:00
             3/2/2010              PL 5                    0:30

and so on...

On the sheet where the data is to be added and placed to populate my chart 
is layed out like this:
The dates are based on the date of the ending week period.
Colomn A           Colomn B         Colomn C           Colomn D
                         3/5/2010         3/12/2010          3/19/2010
PL 1
PL 2
PL 3
PL 4
PL 5

How can i get the info to add up for a week without having to do use the 
SUMIF or SUMIFS based formula selecting the number of cells used for the 5 
day week period at the end of each week?
Let me know if the information I provided was enough to make my self clear.
Your help is greatly appreciated.
Thanks
Peter
0
Utf
3/2/2010 6:42:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
1040 Views

Similar Articles

[PageSpeed] 48

Hi Peter,

Suppose you have original data on sheet 1 and want to calculate 'chart
data' on sheet 2, insert this formula in B2 of sheet2, and copy/paste
the formula to fill the entire table:

=3DSUMPRODUCT(--(Sheet1!$A$1:$A$100>=3DSheet2!B$1-5);--(Sheet1!$A$1:$A
$100<=3DSheet2!B$1);--(Sheet1!$B$1:$B$100=3DSheet2!$A2);Sheet1!$K$1:$K
$100)

Regards,
Per

On 2 Mar., 19:42, Peter Gonzalez
<PeterGonza...@discussions.microsoft.com> wrote:
> Hello,
> Im trying to add the amount of time spent on a type of machinery based on=
 a =A0
> 5 day week period. For example from 3/1/2010 to 3/5/2010 not including
> weekends.
> On the sheet with the data of the amount of time I need added is layed ou=
t
> like this:
> =A0 =A0 =A0 =A0 =A0 =A0 =A0Colomn A =A0 =A0 =A0 =A0 =A0Colomn B =A0 =A0 =
=A0 =A0 =A0 Colomn K
> Example:3/1/2010 =A0 =A0 =A0 =A0 =A0 =A0 =A0PL 1 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A01:54
> =A0 =A0 =A0 =A0 =A0 =A0 =A03/1/2010 =A0 =A0 =A0 =A0 =A0 =A0 =A0PL 5 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A00:13
> =A0 =A0 =A0 =A0 =A0 =A0 =A03/1/2010 =A0 =A0 =A0 =A0 =A0 =A0 =A0PL 1 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A00:07
> =A0 =A0 =A0 =A0 =A0 =A0 =A03/2/2010 =A0 =A0 =A0 =A0 =A0 =A0 =A0PL 5 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A00:13
> =A0 =A0 =A0 =A0 =A0 =A0 =A03/2/2010 =A0 =A0 =A0 =A0 =A0 =A0 =A0PL 1 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01:00
> =A0 =A0 =A0 =A0 =A0 =A0 =A03/2/2010 =A0 =A0 =A0 =A0 =A0 =A0 =A0PL 5 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A00:30
>
> and so on...
>
> On the sheet where the data is to be added and placed to populate my char=
t
> is layed out like this:
> The dates are based on the date of the ending week period.
> Colomn A =A0 =A0 =A0 =A0 =A0 Colomn B =A0 =A0 =A0 =A0 Colomn C =A0 =A0 =
=A0 =A0 =A0 Colomn D
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A03/5/2010 =A0 =A0 =A0 =
=A0 3/12/2010 =A0 =A0 =A0 =A0 =A03/19/2010
> PL 1
> PL 2
> PL 3
> PL 4
> PL 5
>
> How can i get the info to add up for a week without having to do use the
> SUMIF or SUMIFS based formula selecting the number of cells used for the =
5
> day week period at the end of each week?
> Let me know if the information I provided was enough to make my self clea=
r.
> Your help is greatly appreciated.
> Thanks
> Peter

0
Per
3/2/2010 9:29:04 PM
Reply:

Similar Artilces:

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

update column
How would I update a column with numeric values so that there are 3 leading zeros for each row? hi it is not possible to add leading zeros to a numeric value. Mathematically, this is redundent and unnecessary. "brian" wrote: > How would I update a column with numeric values so that there are 3 leading > zeros for each row? opps. hit the post button too quick. option 1. custom format if your numeric value is 12345 then see the custom format to 00000000. note. format do not change data - it just changes the way it looks in the cell. option2. format to text then use the c...

Summing Alternate Rows
We have spreadsheets with many rows and need to sum alternate rows. Is there a way to do this without entering a formula using the number of each row? -- KarenQ ------------------------------------------------------------------------ KarenQ's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19608 View this thread: http://www.excelforum.com/showthread.php?threadid=395115 Try this: =SUMPRODUCT(--(MOD(ROW($A$1:$A$18),2)=1),$A$1:$A$18) That formula sums odd-row values in the range A1:A18. To sum even-row values the formula is: =SUMPRODUCT(--(MOD(ROW($A$1:$A$18...

visual basic
Hi, I trying to retrieve values from a table to calculate the 14days average value of a stock closing price. However, i encounter some problem as stated beside the code as follows: Function DaysAvgs() 'Calculate the average value of a given value. Dim db As DAO.Database Dim rst As DAO.Recordset Dim varBookmark As Variant Dim numAve, numDaysAvg As Double Dim intA, intB, lngCount As Integer Set db = CurrentDb 'Open Table Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable) rst.MoveFirst Do While Not rst.EOF intA = 1 intB = 0 varBookmark = rst.Bookmark n...

How disable postmaster reply in exchange 5.5
I receive mail from virus in my Exchange 5.5 with this format name@mydomain with a virus attached My exchange server reply with postmaster with the original mail attached. I think that this is Mydoom virus. Please help me to disable NDR messages for any e-mail sent to an invalid address. Virus send e-mail with field FROM with addres that want attack and my exchange reply with postmaster with the original mail that contain mydoom virus. Excuse me for my bad english. It is not possible to disable generation of NDR with E55. Why not install antivirus for Exchange? ALEX wrote: > I rece...

Global Column Row Preview Font Size
I know I can change the column, row and preview font size for the current email folder's view, but how do I do it for all of the email folders? I have loads of email addresses each with lots of folders. I don't want to have to do each one at a time. Surely there must be a default font setting (even if it's only in the registry)? Thanks in advance, Tim. I too would love an answer to this. Ian "Timie Milie" <tim_milstead@yahoo.co.uk> wrote in message news:45782ee8$0$27107$db0fefd9@news.zen.co.uk... >I know I can change the column, row and preview font ...

IsOutLookClient() returns wrong value
IsOutLookClient() returns wrong value when both web client of crm and outlook client are running on the same workstation It looks like the same cookie(used for determining what client is running) is used by the sessions of each client. Look for "LightClient" in IsOutlookWorkstationClient() in global.js Oeps...I seem to have made a wrong assumption... Between the to clients IsOutlookClient() seems to work ok... But in outlook client the IsOutlookClient() function gives false for me...after I have opened a page from the Microsoft Crm folder structure... On another workstation it...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

How do I insert space between 2 consecutive columns of an XL Shee.
I want to have space between two consecutive columns of a worksheet (of course, without having inserted another column between the two) in order to have separated the Border Lines of the adjacent cells/columns. Please guide me if it can be done in XL. Can you achieve the effect that you're looking for by using a double vertical border down the right side of the left column and having no border down the left side of the right column? Rgds, ScottO "Shamshad Butt" <Shamshad Butt@discussions.microsoft.com> wrote in message news:1222EE13-11A9-4354-9F12-D1F1155D3902@microsof...

Symbol Updating Only Every Few Days (if at all)
Using Money 2006, and have a symbol "VLO" that is only updating every few days. This stock was a duplicate (downloaded transaction created a new version of the same stock - my fault not clicking correct choice when asked). I've removed the symbol from the stock entry that was downloaded, renamed this entry to something bogus, deleting this renamed stock "from all accounts", then added the symbol back to the original VLO stock that I've been tracking for years. Now the stock just says "unch" in the portfolio view, and the price history is only updat...

sort by day of the week?
Excel 2002 I have 10 years of grain prices...one price for each trading day of th year. In 'A' we have the date, in 'B' the price. What I need to do i get a list of prices for Mondays, Tuesdays, Wednesdays, etc... Possible? (I really, really don't want to do this manually) Than -- Message posted from http://www.ExcelForum.com add a column = weekday(a1,1) etc and do a sort on this colum -- Message posted from http://www.ExcelForum.com Thank you. You saved me 3 or 4 hours of excruciating data entry. Thi was the last step of a vital report. Now I can do my char...

How do I convert a concatenated value into a know value
Hi all I am trying to get the results of a multiple input table, which get concatenated, read out as usable values eg. If the concatenated values are for example *llbbt* , I need this t be read as Simon, or *lbttd* must result in Fred etc... I will attact the spreadsheet. Thanks Colli Attachment filename: book3.xls Download attachment: http://www.excelforum.com/attachment.php?postid=54116 -- Message posted from http://www.ExcelForum.com You are probably better off by describing your problem, most regulars won't open files.. -- Regards, Peo Sjoblo...

Value is BLANK
In a form i'm working on i've asked this question before and i'm unable to locate the replies, but in one cell I have a date to be enter and in the other cell it takes that date and add 5 days to the date to give me a due date. But if no date is enter then I want to to remain blank insted giving me a date. Say that the date is to be entered into A1, then enter this formula into the "other" cell: =IF(A1,A1+5,"") -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ==================...

Null value in form not trapped by beforeupdate event
I have a form in Access 2003 linked to a SQL Server 2005 table. When I clear the value in a textbox (bound field is varchar and is required), I want the before update event to run to tell the user the value cannot be null. When I press the tab button to move to the next field after clearing the texbox, the before update event is not triggering and instead I'm getting the following error: You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162) How can I prevent nulls before and this error from triggering? Thanks! ...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

Sum a group of numbers
When I first started using Excel, if I had a bunch of different totals on a worksheet, I could hold down the right mouse button and cover the group I wanted to add. The sum of these numbers would appear on the bottom of either the worksheet or a task bar. I have lost the ability to do this. Or I have lost the task bar. How do I get it back? Hi Barbie, That would be the status bar. If it is not showing, go to View>Status Bar to turn it on. If the sum function is still not showing, right click anywhere on the status bar and select which option you want. HTH Martin "Barbie...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...

formula to have 0 to 6.5 only in ref to other cell val of 0<=6.5
...

Sum items in a lookup table.
I work for a bus company that has 240 different routes. I also have a table in Excel that lists monthly ridership for each route. I use it as a lookup table to get data for another report. The problem? There are 6 of the 240 routes that are served by more than one garage so they appear twice in the lookup table and I would like to get the sum of the ridership for that particular route from the table. Is this possible and if so how do I do it? I gather you're using a lookup function now to find the ridership from a given route. VLOOKUP will return (0 or) 1 value. If you use SU...

y value vs x
In an xy scatter plot one can choose the type of line connecting the data points (smooth, straight, etc.). Once this is done, Is there a simple way of determining the y value of graph for a specific x value without doing successive approximations with 0 shifts. I'd rather not purchase a graphing program just for this simple task. You could find an equation that fits the data (see trendline) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ralph" <ralph@discussions.microsoft.com> wrote in message news:284B39DE-20C6-40CB-AB60-39B...

Column searching problem
I have a worksheet called "net" containing the following: NETWORK AVG MIN MAX STD SAMPLES ABC 17.17 16.26 17.71 0.4469 19 CBS 12.99 11.69 14.56 0.6524 30 NBC 15.39 14.08 16.70 0.7323 38 NBC* 15.12 14.08 15.99 0.4910 31 Fox 10.63 9.07 12.09 0.9374 8 HBO 10.94 9.07 13.22 1.2507 19 Showtime 11.52 6.84 13.23 1.9017 11 HDNet 18.14 14.22 18.85 1.1619 14 HDNet* 18.45 18.13 18.85 0.2847 13 HDNet-Movies 1...

Moving exchange 5.5 to a new server exchange 2003
Good day all... how do I move exchange 5.5 to a brand new exchange server 2003. The new server will be in a new organization. Any info would be greatly appreciated. Thanks, Ray http://www.microsoft.com/usa/webcasts/ondemand/2340.asp http://support.microsoft.com/default.aspx?scid=fh;en- us;exch2003 Patrick Rouse Microsoft MVP - Terminal Server >-----Original Message----- >Good day all... how do I move exchange 5.5 to a brand new >exchange server 2003. The new server will be in a new >organization. Any info would be greatly appreciated. > >Thanks, >Ray > >. ...

Column help
I have a seating chart I am trying to design for our graduation ceremony. Right now I have the report set up using columns. I have a rectange box with the graduates first, middle and last name in the box. I need 16 columns across the page for the seats which I have working. My problems are that I need a bold line going down the middle to divide the 8th and 9th rows. ( Students come in from 2 lines and meet in the middle of each row.) My next problem is that I need to have another column to the left of the 1st column and one to the right of the 16th column that will count the row num...