Help with If Sum statement - i think....
in [Functions]
|
From: Scott_goddard on 15 Apr 2010 08:38 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 D1:D6, project sheet E1:E6}and{ project sheet"analysis","develop",project}*(projectsheetC1:C6)
From: Duke Carey on 15 Apr 2010 09:39 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 D1:D6, project sheet > E1:E6}and{ > project sheet"analysis","develop",project}*(projectsheetC1:C6)
From: Duke Carey on 15 Apr 2010 09:53 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 D1:D6, project sheet > E1:E6}and{ > project sheet"analysis","develop",project}*(projectsheetC1:C6)
From: Scott_goddard on 15 Apr 2010 11:35 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 D1:D6, project sheet > > E1:E6}and{ > > project sheet"analysis","develop",project}*(projectsheetC1:C6)
From: Duke Carey on 15 Apr 2010 12:26 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!D2:D100)=weeknum(B2)),PROJECTSHEET!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 D1:D6, project sheet > > > E1:E6}and{ > > > project sheet"analysis","develop",project}*(projectsheetC1:C6)
|
Pages: 1 Prev: Can I combine a Vlookup with an IF formula? Next: pounds shillings pence format in excel |