Sum amount of days in another coloumn

[Scott_goddard]

Hi all,

I am using the below spread sheet

Task Working days Start date End date
1 7 days 28/05/2010 07/06/2010
Analysis 1 2 days 28/05/2010 31/05/2010
Develop 2 1 wk 01/06/2010 07/06/2010
2 4 days 01/06/2010 04/06/2010
Analysis 1 1 day 01/06/2010 01/06/2010
Develop 2 3 days 02/06/2010 04/06/2010
3 7 days 02/06/2010 10/06/2010
Analysis 1 2 days 02/06/2010 03/06/2010
Develop 2 1 wk 04/06/2010 10/06/2010

I need to calculator the amount of working days based on if the days are for
analysis or development. I would like to return the value in one cell for
development and analysis work? Any one now how to do this?

 

[Jacob Skaria]

Try the below

ColD and Col E contains dates...

=SUMPRODUCT((A1:A10={"Analysis","Develop"})*(E1:E10-D110))

 

[Roger Govier]

Hi Scott

Any chance you could modify your column B so that 1 wk is displayed as 5
rather than 1?
Also can you get rid of the text in those cells.

If not, then create a new column E with the formula

=IF(ISNUMBER(FIND("w",B2)),LEFT(B2,FIND(" ",B2))
*5,IF(ISNUMBER(FIND("d",B2)),LEFT(B2,FIND(" ",B2)-1)*1,""))

If so, then in F1 enter Analysis and in G1 enter Develop
In F2 enter
=SUMIF($A:$A,F1,$E:$E)
Copy across to G2

If you can alter your column B, then just use
=SUMIF($A:$A,F1,$B:$B)

 

[Scott_goddard]

Hi thanks for this but i can not seem to get it to work....does it matter
that i am not using 2007 - it just returns a value error

 

[Scott_goddard]

Hi just got this to work - sort of. The problem i now have is the col A
contains loads of different titles for different pieces of work and i think
this is causing the Value return. Should this be a problem?

 

[Scott_goddard]

Sorry, i have it working now...the dtae coloumn had a title in it.

 

[Jacob Skaria]

Post few examples of data in ColA..

 

[Scott_goddard]

Go this to work now, all thought i have made some adjustments.

=SUMPRODUCT((A1:A10={"Analysis","Develop"})*(E1:E10-D110))

I have changed the formula to only include one variable so i can return each
variable in a different cell. However i would now like to change the
variable to ref a cell so i could change the title. The problem is when i
change "Analysis to say $M$1" it only returns value however if i change it
back to a word it retruns the correct number - thoughts?

 

[Jacob Skaria]

OK. Try the below which will search for the text string 'Analysis' and
'develop'..

=SUMPRODUCT((ISNUMBER(SEARCH({"Analysis","Develop"},A1:A10)))*
(E1:E10-D110))