If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with If Sum statement - i think....
Hi,
I need to be able to do the following but i can not quite arrange the formula corectly.... I have one sheet that has all the weeks in coulumns (for a project). In another sheet i have this A B C D E Analysis Task 3 12/04/2010 14/04/2010 Task 5 15/04/2010 21/04/2010 Task 3 22/04/2010 26/04/2010 Task 10 27/04/2010 10/05/2010 Task 6 11/05/2010 18/05/2010 Analysis Task 2 11/05/2010 12/05/2010 Develop Task 4 13/05/2010 18/05/2010 Task 3.5 13/05/2010 18/05/2010 Analysis Task 1 13/05/2010 13/05/2010 Develop Task 2.5 14/05/2010 18/05/2010 Task 5.8 14/05/2010 21/05/2010 Analysis Task 2 14/05/2010 17/05/2010 Develop Task 3.8 18/05/2010 21/05/2010 I would like ini the calendar sheet to sum by week and by resources the amount of hours being used...any ideas? Thought this might work....but not sure... Sumif(calendarsheet D1,calendarE1={projectsheet D16, project sheet E1:E6}and{ project sheet"analysis","develop",project}*(projectsheetC1 :C6) |
#2
|
|||
|
|||
Help with If Sum statement - i think....
Well, Scott , first thing you have to do is fix the blanks in column A.
Select all the cells in column A for your data range. Press F5, Alt-s, and choose Blanks & OK. All the empty cells should be selected. Now type the equals sign and cursor up one cell, then press Ctrl-Enter. Next select all your data in column A, copy, and paste-special values. You now have data you can work with. Next, use the WEEKNUM() function in column F to identify the week within which each task's date falls. Finally, use your entire range to create a pivot table "Scott_goddard" wrote: Hi, I need to be able to do the following but i can not quite arrange the formula corectly.... I have one sheet that has all the weeks in coulumns (for a project). In another sheet i have this A B C D E Analysis Task 3 12/04/2010 14/04/2010 Task 5 15/04/2010 21/04/2010 Task 3 22/04/2010 26/04/2010 Task 10 27/04/2010 10/05/2010 Task 6 11/05/2010 18/05/2010 Analysis Task 2 11/05/2010 12/05/2010 Develop Task 4 13/05/2010 18/05/2010 Task 3.5 13/05/2010 18/05/2010 Analysis Task 1 13/05/2010 13/05/2010 Develop Task 2.5 14/05/2010 18/05/2010 Task 5.8 14/05/2010 21/05/2010 Analysis Task 2 14/05/2010 17/05/2010 Develop Task 3.8 18/05/2010 21/05/2010 I would like ini the calendar sheet to sum by week and by resources the amount of hours being used...any ideas? Thought this might work....but not sure... Sumif(calendarsheet D1,calendarE1={projectsheet D16, project sheet E1:E6}and{ project sheet"analysis","develop",project}*(projectsheetC1 :C6) |
#3
|
|||
|
|||
Help with If Sum statement - i think....
Needed to add that the week number should be used as a column label in your
pivot table "Scott_goddard" wrote: Hi, I need to be able to do the following but i can not quite arrange the formula corectly.... I have one sheet that has all the weeks in coulumns (for a project). In another sheet i have this A B C D E Analysis Task 3 12/04/2010 14/04/2010 Task 5 15/04/2010 21/04/2010 Task 3 22/04/2010 26/04/2010 Task 10 27/04/2010 10/05/2010 Task 6 11/05/2010 18/05/2010 Analysis Task 2 11/05/2010 12/05/2010 Develop Task 4 13/05/2010 18/05/2010 Task 3.5 13/05/2010 18/05/2010 Analysis Task 1 13/05/2010 13/05/2010 Develop Task 2.5 14/05/2010 18/05/2010 Task 5.8 14/05/2010 21/05/2010 Analysis Task 2 14/05/2010 17/05/2010 Develop Task 3.8 18/05/2010 21/05/2010 I would like ini the calendar sheet to sum by week and by resources the amount of hours being used...any ideas? Thought this might work....but not sure... Sumif(calendarsheet D1,calendarE1={projectsheet D16, project sheet E1:E6}and{ project sheet"analysis","develop",project}*(projectsheetC1 :C6) |
#4
|
|||
|
|||
Help with If Sum statement - i think....
This information is link from a Gantt chart within excel so i would rather
have something that work from linked cells as it would update its self....the pivot wouldnt. Also the task wouldnt alway be in order, so the table would have repeated weeks - think this would be an issue for the pvit... Any more suggestion regarding the formula option??? "Duke Carey" wrote: Needed to add that the week number should be used as a column label in your pivot table "Scott_goddard" wrote: Hi, I need to be able to do the following but i can not quite arrange the formula corectly.... I have one sheet that has all the weeks in coulumns (for a project). In another sheet i have this A B C D E Analysis Task 3 12/04/2010 14/04/2010 Task 5 15/04/2010 21/04/2010 Task 3 22/04/2010 26/04/2010 Task 10 27/04/2010 10/05/2010 Task 6 11/05/2010 18/05/2010 Analysis Task 2 11/05/2010 12/05/2010 Develop Task 4 13/05/2010 18/05/2010 Task 3.5 13/05/2010 18/05/2010 Analysis Task 1 13/05/2010 13/05/2010 Develop Task 2.5 14/05/2010 18/05/2010 Task 5.8 14/05/2010 21/05/2010 Analysis Task 2 14/05/2010 17/05/2010 Develop Task 3.8 18/05/2010 21/05/2010 I would like ini the calendar sheet to sum by week and by resources the amount of hours being used...any ideas? Thought this might work....but not sure... Sumif(calendarsheet D1,calendarE1={projectsheet D16, project sheet E1:E6}and{ project sheet"analysis","develop",project}*(projectsheetC1 :C6) |
#5
|
|||
|
|||
Help with If Sum statement - i think....
Well, whether by pivot table or formula, you ABSOLUTELY must have repeating
entries in column A. If you want to use formulas, SUMPRODUCT()s will work. Assuming you're trying to sum Analysis tasks and "Analysis" is in column A row 2 and the 'week' date is in B1, use this in B2 =SUMPRODUCT(--(PROJECTSHEET!A2:A100=A2),--(weeknum(PROJECTSHEET!D2100)=weeknum(B2)),PROJEC TSHEET!c2:c100) That may have to be entered as an array formula, by pressing Ctrl-Shift-Enter. You may need to look up WEEKNUM() in help, too. There is an otpional parameter that tells Excel what day of the week is the START of the week, and you may need that to get the dates right "Scott_goddard" wrote: This information is link from a Gantt chart within excel so i would rather have something that work from linked cells as it would update its self....the pivot wouldnt. Also the task wouldnt alway be in order, so the table would have repeated weeks - think this would be an issue for the pvit... Any more suggestion regarding the formula option??? "Duke Carey" wrote: Needed to add that the week number should be used as a column label in your pivot table "Scott_goddard" wrote: Hi, I need to be able to do the following but i can not quite arrange the formula corectly.... I have one sheet that has all the weeks in coulumns (for a project). In another sheet i have this A B C D E Analysis Task 3 12/04/2010 14/04/2010 Task 5 15/04/2010 21/04/2010 Task 3 22/04/2010 26/04/2010 Task 10 27/04/2010 10/05/2010 Task 6 11/05/2010 18/05/2010 Analysis Task 2 11/05/2010 12/05/2010 Develop Task 4 13/05/2010 18/05/2010 Task 3.5 13/05/2010 18/05/2010 Analysis Task 1 13/05/2010 13/05/2010 Develop Task 2.5 14/05/2010 18/05/2010 Task 5.8 14/05/2010 21/05/2010 Analysis Task 2 14/05/2010 17/05/2010 Develop Task 3.8 18/05/2010 21/05/2010 I would like ini the calendar sheet to sum by week and by resources the amount of hours being used...any ideas? Thought this might work....but not sure... Sumif(calendarsheet D1,calendarE1={projectsheet D16, project sheet E1:E6}and{ project sheet"analysis","develop",project}*(projectsheetC1 :C6) |
Thread Tools | |
Display Modes | |
|
|