Help with If Sum statement - i think....

[Scott_goddard]

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)

 

[Duke Carey]

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

 

[Duke Carey]

Needed to add that the week number should be used as a column label in your
pivot table

 

[Scott_goddard]

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]

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)),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