Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Sumproduct - Excel 2003
I am currently working on a spreadsheet where I need to solve two
problems both of which I think can be done with Sumproduct but unfortunately I am having difficulty getting my head round it. I basically want to add up the corresponding entries which satisfy two conditions and then three conditions The worksheet called "chargeablehours" has multiple columns but the four important ones a- Column A - Project ID Column D - CLSStage Column H = Date Column I - Hoursworked In an seperate worksheet "Menu" (within the same workbook) I want to be able to do two seperate calculations using the information input into A1 - input Project ID B1 - input CLSstage C1 - input startdate D1 - input enddate The first formula needs to extract all entries where the ProjectID and CLSstage are equal to A1 and B1 and sum the hoursworked in Column I The second formula needs to extract exactly the same information (sum hoursworked) but where the dates in column H are between the input startdate and enddate. I hope this makes sense Any help would be most appreciated BJthebear |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Sumproduct - Excel 2003
First formula:
=SUMPRODUCT(--(ChangeableHours!A1:A200=Menu!A1),--(ChangeableHours!D1:D200=Menu!A2),ChangeableHours! I1:I200) adjust ranges as needed; you cannot use full column references as in A:A The reference to sheet name Menu is not required =SUMPRODUCT(--(ChangeableHours!A1:A200=A1),--(ChangeableHours!D1:D200=A2),ChangeableHours!I1:I2 00) Could also use named ranges Second Q is ambiguous either =SUMPRODUCT(--(ChangeableHours!A1:A200=Menu!A1),--(ChangeableHours!D1:D020=Menu!A2),--(ChangeableHours!H1:H200=Menu!A3),--(ChangeableHours!H1:H20=Menu!A4),ChangeableHours!I 1:I200) or =SUMPRODUCT(--(ChangeableHours!H1:H200=Menu!A3),--(ChangeableHours!H1:H200=Menu!A4),ChangeableHours! I1:I200) For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html Debra Dalgleish http://www.contextures.com/xlFunctio...tml#SumProduct best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "BJ&theBear" wrote in message ... I am currently working on a spreadsheet where I need to solve two problems both of which I think can be done with Sumproduct but unfortunately I am having difficulty getting my head round it. I basically want to add up the corresponding entries which satisfy two conditions and then three conditions The worksheet called "chargeablehours" has multiple columns but the four important ones a- Column A - Project ID Column D - CLSStage Column H = Date Column I - Hoursworked In an seperate worksheet "Menu" (within the same workbook) I want to be able to do two seperate calculations using the information input into A1 - input Project ID B1 - input CLSstage C1 - input startdate D1 - input enddate The first formula needs to extract all entries where the ProjectID and CLSstage are equal to A1 and B1 and sum the hoursworked in Column I The second formula needs to extract exactly the same information (sum hoursworked) but where the dates in column H are between the input startdate and enddate. I hope this makes sense Any help would be most appreciated BJthebear |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Sumproduct - Excel 2003
Thanks for the help
I fully understand the first part but cannot fathom out how the second part actually calculates whether the date in Column H is between the startdate and enddate. To my "simple mind" all it is doing is checking for entries with the same start date returning true or false and then doing the same with the end date and as there is only one date in column H it will never be able to come up with 2 x true and it will always return false as there is only one date - this will mean that it cannot find any matches??!! Am I missing something here? Thanks once again BJthebear On Apr 13, 3:55*pm, "Bernard Liengme" wrote: First formula: =SUMPRODUCT(--(ChangeableHours!A1:A200=Menu!A1),--(ChangeableHours!D1:D200=*Menu!A2),ChangeableHours !I1:I200) adjust ranges as needed; you cannot use full column references as in A:A The reference to sheet name Menu is not required =SUMPRODUCT(--(ChangeableHours!A1:A200=A1),--(ChangeableHours!D1:D200=A2),C*hangeableHours!I1:I 200) Could also use named ranges Second Q is ambiguous either =SUMPRODUCT(--(ChangeableHours!A1:A200=Menu!A1),--(ChangeableHours!D1:D020=*Menu!A2),--(ChangeableHours!H1:H200=Menu!A3),--(ChangeableHours!H1:H20=Men*u!A4),ChangeableHours! I1:I200) or =SUMPRODUCT(--(ChangeableHours!H1:H200=Menu!A3),--(ChangeableHours!H1:H200*=Menu!A4),ChangeableHours !I1:I200) For more details on SUMPRODUCT Bob Phillipshttp://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpseyhttp://mcgimpsey.com/excel/formulae/doubleneg.html Debra Dalgleishhttp://www.contextures.com/xlFunctions04.html#SumProduct best wishes -- Bernard Liengme Microsoft Excel MVPhttp://people.stfx.ca/bliengme "BJ&theBear" wrote in message ... I am currently working on a spreadsheet where I need to solve two problems both of which I think can be done with Sumproduct but unfortunately I am having difficulty getting my head round it. I basically want to add up the corresponding entries which satisfy two conditions and then three conditions The worksheet called "chargeablehours" has multiple columns but the four important ones a- Column A * - Project ID Column D *- *CLSStage Column H *= *Date Column I * - *Hoursworked In an seperate worksheet "Menu" (within the same workbook) I want to be able to do two seperate calculations using the information input into A1 *- input Project ID B1 *- input CLSstage C1 - input startdate D1 *- input enddate The first formula needs to extract all entries where the ProjectID and CLSstage are equal to A1 and B1 and sum the hoursworked in Column I The second formula needs to extract exactly the same information (sum hoursworked) but where the dates in column H are between the input startdate and enddate. I hope this makes sense Any help would be most appreciated BJthebear- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Sumproduct - Excel 2003
Sorry, I misread your question: I had this info
A1 - input Project ID B1 - input CLSstage C1 - input startdate D1 - input enddate as A1 - input Project ID A2 - input CLSstage A3 - input startdate A4 - input enddate So we need to change =SUMPRODUCT(--(ChangeableHours!H1:H200=A3),--(ChangeableHours!H1:H200=A4),ChangeableHours!I1:I2 00) to =SUMPRODUCT(--(ChangeableHours!H1:H200=C1),--(ChangeableHours!H1:H200<=D1),ChangeableHours!I1:I 200) I had also omitted a "<" in the second argument when I copied this formula from my test worksheet. So what this does is: First argument) if the date in H is equal to or greater than C1 we get a 1, else we get a zero. Suppose for the first five records we get {0,0,1,1,1} - the last three on or after C1's date Second argument) if the date in H is equal to or less than D1 we get a 1, else we get a zero. Suppose for the first five records we get {1,1,1,1,0} - the first 4 are on of before D1's date When these get multiplied {0,0,1,1,1}* {1,1,1,1,0} ={0,0,1,1,0} -- records 3 and 4 lie between the two dates (inclusively) Third argument) returns hours worked: so {20,30,20,40} Multiple this by the result of first two: ={0,0,1,1,0}*{20,30,20,40} = {0,0,30,20,0} We have run out of arguments, so SUMPRODUCT adds the array {0 +0 +30 +20 + 0} = 50 This sums the hours for the record that lie between two dates best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "BJ&theBear" wrote in message ... Thanks for the help I fully understand the first part but cannot fathom out how the second part actually calculates whether the date in Column H is between the startdate and enddate. To my "simple mind" all it is doing is checking for entries with the same start date returning true or false and then doing the same with the end date and as there is only one date in column H it will never be able to come up with 2 x true and it will always return false as there is only one date - this will mean that it cannot find any matches??!! Am I missing something here? Thanks once again BJthebear On Apr 13, 3:55 pm, "Bernard Liengme" wrote: First formula: =SUMPRODUCT(--(ChangeableHours!A1:A200=Menu!A1),--(ChangeableHours!D1:D200=*Menu!A2),ChangeableHours !I1:I200) adjust ranges as needed; you cannot use full column references as in A:A The reference to sheet name Menu is not required =SUMPRODUCT(--(ChangeableHours!A1:A200=A1),--(ChangeableHours!D1:D200=A2),C*hangeableHours!I1:I 200) Could also use named ranges Second Q is ambiguous either =SUMPRODUCT(--(ChangeableHours!A1:A200=Menu!A1),--(ChangeableHours!D1:D020=*Menu!A2),--(ChangeableHours!H1:H200=Menu!A3),--(ChangeableHours!H1:H20=Men*u!A4),ChangeableHours! I1:I200) or =SUMPRODUCT(--(ChangeableHours!H1:H200=Menu!A3),--(ChangeableHours!H1:H200*=Menu!A4),ChangeableHours !I1:I200) For more details on SUMPRODUCT Bob Phillipshttp://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpseyhttp://mcgimpsey.com/excel/formulae/doubleneg.html Debra Dalgleishhttp://www.contextures.com/xlFunctions04.html#SumProduct best wishes -- Bernard Liengme Microsoft Excel MVPhttp://people.stfx.ca/bliengme "BJ&theBear" wrote in message ... I am currently working on a spreadsheet where I need to solve two problems both of which I think can be done with Sumproduct but unfortunately I am having difficulty getting my head round it. I basically want to add up the corresponding entries which satisfy two conditions and then three conditions The worksheet called "chargeablehours" has multiple columns but the four important ones a- Column A - Project ID Column D - CLSStage Column H = Date Column I - Hoursworked In an seperate worksheet "Menu" (within the same workbook) I want to be able to do two seperate calculations using the information input into A1 - input Project ID B1 - input CLSstage C1 - input startdate D1 - input enddate The first formula needs to extract all entries where the ProjectID and CLSstage are equal to A1 and B1 and sum the hoursworked in Column I The second formula needs to extract exactly the same information (sum hoursworked) but where the dates in column H are between the input startdate and enddate. I hope this makes sense Any help would be most appreciated BJthebear- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't get Sumproduct to work: Excel 2003 | Excel Discussion (Misc queries) | |||
Sumproduct in Excel 2003 | Excel Worksheet Functions | |||
Sumproduct in Excel 2003 | Excel Worksheet Functions | |||
SUMPRODUCT FORMULA EXCEL 2003 | Excel Worksheet Functions | |||
Excel 2003 Multi Worksheet Sumproduct? | Excel Worksheet Functions |