Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How t count by week whatever is with in the week (two criteria, ma
I have a Date Colum, its first row begins with 01 Oct 06 and ends with 01
Oct 08. I have another column Type of Work that has three types of work, SR, SO and WR I would like to draw a total number of Work Orders per week and type of work Order (results on the other sheet) e.e: Weekending SR SO WO 07-Oct-06 = 10 45 15 14-Oct-06 = 30 56 102 etc.. I tried with Pivot Table but I can cot brake them down by weeks. Is there other way of working this out please? Perhaps formula, PivotTable or maybe code? Thank you, Adnan -- Please post all your inquiries on this community so we can all benefit - Thank you! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How t count by week whatever is with in the week (two criteria, ma
=SUMPRODUCT(--(Sheet1!$A$1:$A$1000$A2-7),--(Sheet1!$A$1:$A$1000<=$A2),--(Sh
eet1!$B$1:$B$1000=B$1)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Adnan" (donotspam) wrote in message ... I have a "Date" Colum, its first row begins with 01 Oct 06 and ends with 01 Oct 08. I have another column "Type of Work" that has three types of work, SR, SO and WR I would like to draw a total number of Work Orders per week and type of work Order (results on the other sheet) e.e: Weekending SR SO WO 07-Oct-06 = 10 45 15 14-Oct-06 = 30 56 102 etc... I tried with Pivot Table but I can cot brake them down by weeks. Is there other way of working this out please? Perhaps formula, PivotTable or maybe code? Thank you, Adnan -- Please post all your inquiries on this community so we can all benefit - Thank you! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How t count by week whatever is with in the week (two criteria
Bob,
I appreciate your quick response. I tried it, it gives me a number but I dont really seam to know where its coming form nor what its calculating? Where do I paste this formula? Note: Sheet1 has the data, and sheet2 should have results, I have the format on sheet2 that looks this way: Weekend SRs SOs WRs 07-Oct-06 [formula here for each for each work type (SR, SO, WR)] 14-Oct-06 21-Oct-06 Thank you again, Adnan -- Please post all your inquiries on this community so we can all benefit - Thank you! "Bob Phillips" wrote: =SUMPRODUCT(--(Sheet1!$A$1:$A$1000$A2-7),--(Sheet1!$A$1:$A$1000<=$A2),--(Sh eet1!$B$1:$B$1000=B$1)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Adnan" (donotspam) wrote in message ... I have a "Date" Colum, its first row begins with 01 Oct 06 and ends with 01 Oct 08. I have another column "Type of Work" that has three types of work, SR, SO and WR I would like to draw a total number of Work Orders per week and type of work Order (results on the other sheet) e.e: Weekending SR SO WO 07-Oct-06 = 10 45 15 14-Oct-06 = 30 56 102 etc... I tried with Pivot Table but I can cot brake them down by weeks. Is there other way of working this out please? Perhaps formula, PivotTable or maybe code? Thank you, Adnan -- Please post all your inquiries on this community so we can all benefit - Thank you! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How t count by week whatever is with in the week (two criteria
That is exactly how I read your original post Adnan.
Paste the formula into B2 on sheet2, and copy down and across. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Adnan" (donotspam) wrote in message ... Bob, I appreciate your quick response. I tried it, it gives me a number but I don't really seam to know where it's coming form nor what it's calculating? Where do I paste this formula? Note: Sheet1 has the data, and sheet2 should have results, I have the format on sheet2 that looks this way: Weekend SRs SOs WRs 07-Oct-06 [formula here for each for each work type (SR, SO, WR)] 14-Oct-06 . 21-Oct-06 . Thank you again, Adnan -- Please post all your inquiries on this community so we can all benefit - Thank you! "Bob Phillips" wrote: =SUMPRODUCT(--(Sheet1!$A$1:$A$1000$A2-7),--(Sheet1!$A$1:$A$1000<=$A2),--(Sh eet1!$B$1:$B$1000=B$1)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Adnan" (donotspam) wrote in message ... I have a "Date" Colum, its first row begins with 01 Oct 06 and ends with 01 Oct 08. I have another column "Type of Work" that has three types of work, SR, SO and WR I would like to draw a total number of Work Orders per week and type of work Order (results on the other sheet) e.e: Weekending SR SO WO 07-Oct-06 = 10 45 15 14-Oct-06 = 30 56 102 etc... I tried with Pivot Table but I can cot brake them down by weeks. Is there other way of working this out please? Perhaps formula, PivotTable or maybe code? Thank you, Adnan -- Please post all your inquiries on this community so we can all benefit - Thank you! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How t count by week whatever is with in the week (two criteria
Bob,
I appreciate your efforts. I cant seam to make it work, though. How about, I change my question (this would give me greater results) I have column B that has dates. What formula should I use to return week number in column A? e.g: A B ____________ 1 01-Oct-06 1 01-Oct-06 1 01-Oct-06 2 08-Oct-06 2 10-Oct-06 2 11-Oct-06 2 14-Oct-06 3 05-Oct-06 3 08-Oct-06 .. .. .. Again, thank you! Adnan :-) -- Please post all your inquiries on this community so we can all benefit - Thank you! "Bob Phillips" wrote: That is exactly how I read your original post Adnan. Paste the formula into B2 on sheet2, and copy down and across. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Adnan" (donotspam) wrote in message ... Bob, I appreciate your quick response. I tried it, it gives me a number but I don't really seam to know where it's coming form nor what it's calculating? Where do I paste this formula? Note: Sheet1 has the data, and sheet2 should have results, I have the format on sheet2 that looks this way: Weekend SRs SOs WRs 07-Oct-06 [formula here for each for each work type (SR, SO, WR)] 14-Oct-06 . 21-Oct-06 . Thank you again, Adnan -- Please post all your inquiries on this community so we can all benefit - Thank you! "Bob Phillips" wrote: =SUMPRODUCT(--(Sheet1!$A$1:$A$1000$A2-7),--(Sheet1!$A$1:$A$1000<=$A2),--(Sh eet1!$B$1:$B$1000=B$1)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Adnan" (donotspam) wrote in message ... I have a "Date" Colum, its first row begins with 01 Oct 06 and ends with 01 Oct 08. I have another column "Type of Work" that has three types of work, SR, SO and WR I would like to draw a total number of Work Orders per week and type of work Order (results on the other sheet) e.e: Weekending SR SO WO 07-Oct-06 = 10 45 15 14-Oct-06 = 30 56 102 etc... I tried with Pivot Table but I can cot brake them down by weeks. Is there other way of working this out please? Perhaps formula, PivotTable or maybe code? Thank you, Adnan -- Please post all your inquiries on this community so we can all benefit - Thank you! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How t count by week whatever is with in the week (two criteria
How do you determine that 01-Oct is week 1?
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Adnan" (donotspam) wrote in message ... Bob, I appreciate your efforts. I can't seam to make it work, though. How about, I change my question. (this would give me greater results) I have column B that has dates. What formula should I use to return week number in column A? e.g: A B ____________ 1 01-Oct-06 1 01-Oct-06 1 01-Oct-06 2 08-Oct-06 2 10-Oct-06 2 11-Oct-06 2 14-Oct-06 3 05-Oct-06 3 08-Oct-06 . . . Again, thank you! Adnan :-) -- Please post all your inquiries on this community so we can all benefit - Thank you! "Bob Phillips" wrote: That is exactly how I read your original post Adnan. Paste the formula into B2 on sheet2, and copy down and across. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Adnan" (donotspam) wrote in message ... Bob, I appreciate your quick response. I tried it, it gives me a number but I don't really seam to know where it's coming form nor what it's calculating? Where do I paste this formula? Note: Sheet1 has the data, and sheet2 should have results, I have the format on sheet2 that looks this way: Weekend SRs SOs WRs 07-Oct-06 [formula here for each for each work type (SR, SO, WR)] 14-Oct-06 . 21-Oct-06 . Thank you again, Adnan -- Please post all your inquiries on this community so we can all benefit - Thank you! "Bob Phillips" wrote: =SUMPRODUCT(--(Sheet1!$A$1:$A$1000$A2-7),--(Sheet1!$A$1:$A$1000<=$A2),--(Sh eet1!$B$1:$B$1000=B$1)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Adnan" (donotspam) wrote in message ... I have a "Date" Colum, its first row begins with 01 Oct 06 and ends with 01 Oct 08. I have another column "Type of Work" that has three types of work, SR, SO and WR I would like to draw a total number of Work Orders per week and type of work Order (results on the other sheet) e.e: Weekending SR SO WO 07-Oct-06 = 10 45 15 14-Oct-06 = 30 56 102 etc... I tried with Pivot Table but I can cot brake them down by weeks. Is there other way of working this out please? Perhaps formula, PivotTable or maybe code? Thank you, Adnan -- Please post all your inquiries on this community so we can all benefit - Thank you! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How t count by week whatever is with in the week (two criteria, ma
It is possible to group data by weeks in the pivot table if all entries
in the column are valid dates: After you create the pivot table right click the date column and select the group by option from the short cut menu (or use the toolbar green arrow button). For the group by options check days, for No of days enter 7, and then enter start date probably first monday in the range Adnan (donotspam) wrote: I have a "Date" Colum, its first row begins with 01 Oct 06 and ends with 01 Oct 08. I have another column "Type of Work" that has three types of work, SR, SO and WR I would like to draw a total number of Work Orders per week and type of work Order (results on the other sheet) e.e: Weekending SR SO WO 07-Oct-06 = 10 45 15 14-Oct-06 = 30 56 102 etc..... I tried with Pivot Table but I can cot brake them down by weeks. Is there other way of working this out please? Perhaps formula, PivotTable or maybe code? Thank you, Adnan -- Please post all your inquiries on this community so we can all benefit - Thank you! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How t count by week whatever is with in the week (two criteria
Bob,
Just performed another search and this is what did the job. Thank you for you efforts though. Assuming that the date is in B1, you paste the following formula in A1. This turns the number of the week: =TRUNC(((B1-DATE(YEAR(B1),1,0))+6)/7) Lori, I would still like to perform your tip but I can't. I dont' get that 'Group by' option in first place, there is another one that says just Group but that's for rows and columns. I am using Excel 2003 version. Its not because of the version, is it? BTW, I am interested in buying a book that teaches PivotTables. Any advice on what book would be the best as far as Excel 2003 concerns? Thank you much! Adnan :-) -- Please post all your inquiries on this community so we can all benefit - Thank you! "Lori" wrote: It is possible to group data by weeks in the pivot table if all entries in the column are valid dates: After you create the pivot table right click the date column and select the group by option from the short cut menu (or use the toolbar green arrow button). For the group by options check days, for No of days enter 7, and then enter start date probably first monday in the range Adnan (donotspam) wrote: I have a "Date" Colum, its first row begins with 01 Oct 06 and ends with 01 Oct 08. I have another column "Type of Work" that has three types of work, SR, SO and WR I would like to draw a total number of Work Orders per week and type of work Order (results on the other sheet) e.e: Weekending SR SO WO 07-Oct-06 = 10 45 15 14-Oct-06 = 30 56 102 etc..... I tried with Pivot Table but I can cot brake them down by weeks. Is there other way of working this out please? Perhaps formula, PivotTable or maybe code? Thank you, Adnan -- Please post all your inquiries on this community so we can all benefit - Thank you! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How t count by week whatever is with in the week (two criteria
This worked for me, with your data below:
Date No 01-Oct-06 1 01-Oct-06 1 01-Oct-06 1 08-Oct-06 2 10-Oct-06 2 11-Oct-06 2 14-Oct-06 2 05-Oct-06 3 08-Oct-06 3 Pivot table Group options: By: Days, Starting at: 25/09/2006 (Monday before start date) Sum of No Date Total 25/09/2006 - 01/10/2006 3 02/10/2006 - 08/10/2006 8 09/10/2006 - 15/10/2006 6 I find the best way to learn is to experiment, make a small table and try the various different options. Don't be afraid, if you make a mistake you can undo but always save a separate copy of your raw data which you can refer back to and try different methods on. Get new ideas by following up suggestions in books, newsgroups or websites, I can't refer you to any specific books on pivot tables but Walkenbach is always reliable.. Adnan (donotspam) wrote: Bob, Just performed another search and this is what did the job. Thank you for you efforts though. Assuming that the date is in B1, you paste the following formula in A1. This turns the number of the week: =TRUNC(((B1-DATE(YEAR(B1),1,0))+6)/7) Lori, I would still like to perform your tip but I can't. I don't' get that 'Group by' option in first place, there is another one that says just 'Group' but that's for rows and columns. I am using Excel 2003 version. It's not because of the version, is it? BTW, I am interested in buying a book that teaches PivotTables. Any advice on what book would be the best as far as Excel 2003 concerns? Thank you much! Adnan :-) -- Please post all your inquiries on this community so we can all benefit - Thank you! "Lori" wrote: It is possible to group data by weeks in the pivot table if all entries in the column are valid dates: After you create the pivot table right click the date column and select the group by option from the short cut menu (or use the toolbar green arrow button). For the group by options check days, for No of days enter 7, and then enter start date probably first monday in the range Adnan (donotspam) wrote: I have a "Date" Colum, its first row begins with 01 Oct 06 and ends with 01 Oct 08. I have another column "Type of Work" that has three types of work, SR, SO and WR I would like to draw a total number of Work Orders per week and type of work Order (results on the other sheet) e.e: Weekending SR SO WO 07-Oct-06 = 10 45 15 14-Oct-06 = 30 56 102 etc..... I tried with Pivot Table but I can cot brake them down by weeks. Is there other way of working this out please? Perhaps formula, PivotTable or maybe code? Thank you, Adnan -- Please post all your inquiries on this community so we can all benefit - Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count if two columns match different criteria | Excel Worksheet Functions | |||
update week to week in excel. | Excel Worksheet Functions | |||
Count Unique Values with Multiple Criteria | Excel Worksheet Functions | |||
Sum Count of Criteria Every 3rd Row | Excel Worksheet Functions | |||
I need to count while using multiple criteria | Excel Worksheet Functions |