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

S

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 D1:D6, project sheet
E1:E6}and{
project sheet"analysis","develop",project}*(projectsheetC1:C6)
 
D

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
 
D

Duke Carey

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

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???
 
D

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!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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top