| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sort one coloumn according to another coloumn on another worksheet | Excel Worksheet Functions | |||
| Calculating unearned amount billed 45 days in advance... | New Users to Excel | |||
| Calculate Count of Days & Average WHERE Amount <0 | Excel Worksheet Functions | |||
| set a warning in excel after a certain amount of days | Excel Discussion (Misc queries) | |||
| amount of working days per month | Excel Discussion (Misc queries) | |||
Linear Mode
