Sum amount of days in another coloumn
 

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?

Sum amount of days in another coloumn
 

Try the below

ColD and Col E contains dates...

=SUMPRODUCT((A1:A10={"Analysis","Develop"})*(E1:E1 0-D1:D10))

--
Jacob (MVP - Excel)


"Scott_goddard" wrote:

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?

Sum amount of days in another coloumn
 

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)

--
Regards
Roger Govier

Scott_goddard wrote:
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?

Sum amount of days in another coloumn
 

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

"Jacob Skaria" wrote:

Try the below

ColD and Col E contains dates...

=SUMPRODUCT((A1:A10={"Analysis","Develop"})*(E1:E1 0-D1:D10))

--
Jacob (MVP - Excel)


"Scott_goddard" wrote:

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?

Sum amount of days in another coloumn
 

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" wrote:

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

"Jacob Skaria" wrote:

Try the below

ColD and Col E contains dates...

=SUMPRODUCT((A1:A10={"Analysis","Develop"})*(E1:E1 0-D1:D10))

--
Jacob (MVP - Excel)


"Scott_goddard" wrote:

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?

Sum amount of days in another coloumn
 

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

"Scott_goddard" wrote:

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

"Jacob Skaria" wrote:

Try the below

ColD and Col E contains dates...

=SUMPRODUCT((A1:A10={"Analysis","Develop"})*(E1:E1 0-D1:D10))

--
Jacob (MVP - Excel)


"Scott_goddard" wrote:

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?

Sum amount of days in another coloumn
 

Post few examples of data in ColA..

--
Jacob (MVP - Excel)


"Scott_goddard" wrote:

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" wrote:

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

"Jacob Skaria" wrote:

Try the below

ColD and Col E contains dates...

=SUMPRODUCT((A1:A10={"Analysis","Develop"})*(E1:E1 0-D1:D10))

--
Jacob (MVP - Excel)


"Scott_goddard" wrote:

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?

Sum amount of days in another coloumn
 

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

=SUMPRODUCT((A1:A10={"Analysis","Develop"})*(E1:E1 0-D1:D10))

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" wrote:

Post few examples of data in ColA..

--
Jacob (MVP - Excel)


"Scott_goddard" wrote:

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" wrote:

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

"Jacob Skaria" wrote:

Try the below

ColD and Col E contains dates...

=SUMPRODUCT((A1:A10={"Analysis","Develop"})*(E1:E1 0-D1:D10))

--
Jacob (MVP - Excel)


"Scott_goddard" wrote:

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?

Sum amount of days in another coloumn
 

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

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

--
Jacob (MVP - Excel)


"Scott_goddard" wrote:

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

=SUMPRODUCT((A1:A10={"Analysis","Develop"})*(E1:E1 0-D1:D10))

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" wrote:

Post few examples of data in ColA..

--
Jacob (MVP - Excel)


"Scott_goddard" wrote:

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" wrote:

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

"Jacob Skaria" wrote:

Try the below

ColD and Col E contains dates...

=SUMPRODUCT((A1:A10={"Analysis","Develop"})*(E1:E1 0-D1:D10))

--
Jacob (MVP - Excel)


"Scott_goddard" wrote:

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?