Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nest 3 SUMPRODUCT formulas - Possible?
Hi!
I have a book in Excel 2003 that serves as simple timetable and payroll at the same time, but assignments are scheduled weekly from Mon Sun, which is also the way the employees are paid. That part works perfectly until I have to do the tax analysis, since its breakdown goes by month and year. I have 12 sheets, each for a month in a year and each sheet is made out of 5 weeks. I created them like this because the last days of a month and the beginning of the next can fall on the same week - either on the 5th week of that month or on the 1st of the next. The five weeks also gives the users enough flexibility to organize their weeks as they please. The amount paid each month is figured out from columns C2:C13 (Payments) and D2:D13 (Extras) by their dates in column B2:B13 (this are just a few of the cells in each range, but their similar) with the following formula: =SUMAPRODUCTO(--(B$2:B$13=A19);--(B$2:B$13<=A20);(C$2:C$13+D$2:D$13)) The CATCH is that the prior and/or following sheet might also hold dates from this month. So assuming we're looking at sheet4, the other two are sheet3 and sheet5. My Excel is in Spanish so SUMAPRODUCTO is SUMPRODUCT and Sheet is Hoja. SUMAPRODUCTO(--(Hoja3!B2:B13=A19);--(Hoja3!B2:B13<=A20);(Hoja3!C$2:C$13+Hoja3!D$2:D$13 )) SUMAPRODUCTO(--(Hoja5!B$2:B1$3=A19);--(Hoja5!B$2:B$13<=A$20);(Hoja5!C$2:C$13+Hoja5!D$2:D $13)) A19 and A20 are equivalent to the first and last date of the month and I used them as criteria for the date search. Hoja3 is the prior month and Hoja5 is the following I dont want to have 48 formulas to contend with, but only 12. Is there any way to nest 3 3D SUMPRODUCT formulas in one that sums their product? B21 would hold this formula and its product. Something like SP(SP()+SP()+SP()) or maybe SUM(SP();SP();SP()) Any ideas? I remember seeing this formula somewhere and thats why I ask: =SUMPRODUCT((B1:B10="Europe")+(C1:C10="Y"))-SUMPRODUCT(--(B1:B10="Europe"),--(C1:C10="Y")) I would really appreciate any help. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nest 3 SUMPRODUCT formulas - Possible?
Bert,
You cannot use a 3D SUMPRODUCT formula - unfortunately, your formulas will need to be complex to deal with your workbook structure. I also want to point out that you really need six weeks for your months, not five, since some months start with one or two days in their "first week" and end up with one or two days in their last week, after four full weeks, - so six, not five, weeks overall. But the best solution to all this is to use just one sheet as your database, and learn how to use filters, subtotals, and/or pivot tables to show and report your data instead of formulas. Of course, figuring that out will take some doing, but it would be a better application. HTH, Bernie MS Excel MVP "Bert T" wrote in message ... Hi! I have a book in Excel 2003 that serves as simple timetable and payroll at the same time, but assignments are scheduled weekly from Mon Sun, which is also the way the employees are paid. That part works perfectly until I have to do the tax analysis, since its breakdown goes by month and year. I have 12 sheets, each for a month in a year and each sheet is made out of 5 weeks. I created them like this because the last days of a month and the beginning of the next can fall on the same week - either on the 5th week of that month or on the 1st of the next. The five weeks also gives the users enough flexibility to organize their weeks as they please. The amount paid each month is figured out from columns C2:C13 (Payments) and D2:D13 (Extras) by their dates in column B2:B13 (this are just a few of the cells in each range, but their similar) with the following formula: =SUMAPRODUCTO(--(B$2:B$13=A19);--(B$2:B$13<=A20);(C$2:C$13+D$2:D$13)) The CATCH is that the prior and/or following sheet might also hold dates from this month. So assuming we're looking at sheet4, the other two are sheet3 and sheet5. My Excel is in Spanish so SUMAPRODUCTO is SUMPRODUCT and Sheet is Hoja. SUMAPRODUCTO(--(Hoja3!B2:B13=A19);--(Hoja3!B2:B13<=A20);(Hoja3!C$2:C$13+Hoja3!D$2:D$13 )) SUMAPRODUCTO(--(Hoja5!B$2:B1$3=A19);--(Hoja5!B$2:B$13<=A$20);(Hoja5!C$2:C$13+Hoja5!D$2:D $13)) A19 and A20 are equivalent to the first and last date of the month and I used them as criteria for the date search. Hoja3 is the prior month and Hoja5 is the following I dont want to have 48 formulas to contend with, but only 12. Is there any way to nest 3 3D SUMPRODUCT formulas in one that sums their product? B21 would hold this formula and its product. Something like SP(SP()+SP()+SP()) or maybe SUM(SP();SP();SP()) Any ideas? I remember seeing this formula somewhere and thats why I ask: =SUMPRODUCT((B1:B10="Europe")+(C1:C10="Y"))-SUMPRODUCT(--(B1:B10="Europe"),--(C1:C10="Y")) I would really appreciate any help. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nest 3 SUMPRODUCT formulas - Possible?
Holy crap! You're making this much more difficult than it has to be. Simple
copy/paste everything onto one single sheet and build a Pivot tbale off of that sheet. Run this code to get everything onto one sheet: http://www.rondebruin.nl/copy2.htm If you need help with getting to the last used cell, or ignoring certain sheets, post in the Excel Programming section. When everything is on one summary sheet, Ron calls it the 'RDBMergeSheet' build a Pivot Table; Pivot Tables will do exactly what the Sumproduct functions will do (and a whole lot more too). http://datapigtechnologies.com/flashfiles/pivot1.html http://datapigtechnologies.com/flashfiles/pivot6.html http://peltiertech.com/Excel/Pivots/pivottables.htm Good luck, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Bernie Deitrick" wrote: Bert, You cannot use a 3D SUMPRODUCT formula - unfortunately, your formulas will need to be complex to deal with your workbook structure. I also want to point out that you really need six weeks for your months, not five, since some months start with one or two days in their "first week" and end up with one or two days in their last week, after four full weeks, - so six, not five, weeks overall. But the best solution to all this is to use just one sheet as your database, and learn how to use filters, subtotals, and/or pivot tables to show and report your data instead of formulas. Of course, figuring that out will take some doing, but it would be a better application. HTH, Bernie MS Excel MVP "Bert T" wrote in message ... Hi! I have a book in Excel 2003 that serves as simple timetable and payroll at the same time, but assignments are scheduled weekly from Mon Sun, which is also the way the employees are paid. That part works perfectly until I have to do the tax analysis, since its breakdown goes by month and year. I have 12 sheets, each for a month in a year and each sheet is made out of 5 weeks. I created them like this because the last days of a month and the beginning of the next can fall on the same week - either on the 5th week of that month or on the 1st of the next. The five weeks also gives the users enough flexibility to organize their weeks as they please. The amount paid each month is figured out from columns C2:C13 (Payments) and D2:D13 (Extras) by their dates in column B2:B13 (this are just a few of the cells in each range, but their similar) with the following formula: =SUMAPRODUCTO(--(B$2:B$13=A19);--(B$2:B$13<=A20);(C$2:C$13+D$2:D$13)) The CATCH is that the prior and/or following sheet might also hold dates from this month. So assuming we're looking at sheet4, the other two are sheet3 and sheet5. My Excel is in Spanish so SUMAPRODUCTO is SUMPRODUCT and Sheet is Hoja. SUMAPRODUCTO(--(Hoja3!B2:B13=A19);--(Hoja3!B2:B13<=A20);(Hoja3!C$2:C$13+Hoja3!D$2:D$13 )) SUMAPRODUCTO(--(Hoja5!B$2:B1$3=A19);--(Hoja5!B$2:B$13<=A$20);(Hoja5!C$2:C$13+Hoja5!D$2:D $13)) A19 and A20 are equivalent to the first and last date of the month and I used them as criteria for the date search. Hoja3 is the prior month and Hoja5 is the following I dont want to have 48 formulas to contend with, but only 12. Is there any way to nest 3 3D SUMPRODUCT formulas in one that sums their product? B21 would hold this formula and its product. Something like SP(SP()+SP()+SP()) or maybe SUM(SP();SP();SP()) Any ideas? I remember seeing this formula somewhere and thats why I ask: =SUMPRODUCT((B1:B10="Europe")+(C1:C10="Y"))-SUMPRODUCT(--(B1:B10="Europe"),--(C1:C10="Y")) I would really appreciate any help. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nest 3 SUMPRODUCT formulas - Possible?
Actually, that may not work, since the workbook as described may not merge
into a standard database - data points are duplicated, so more work may (or may not) be needed. Bernie "ryguy7272" wrote in message ... Holy crap! You're making this much more difficult than it has to be. Simple copy/paste everything onto one single sheet and build a Pivot tbale off of that sheet. Run this code to get everything onto one sheet: http://www.rondebruin.nl/copy2.htm If you need help with getting to the last used cell, or ignoring certain sheets, post in the Excel Programming section. When everything is on one summary sheet, Ron calls it the 'RDBMergeSheet' build a Pivot Table; Pivot Tables will do exactly what the Sumproduct functions will do (and a whole lot more too). http://datapigtechnologies.com/flashfiles/pivot1.html http://datapigtechnologies.com/flashfiles/pivot6.html http://peltiertech.com/Excel/Pivots/pivottables.htm Good luck, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Bernie Deitrick" wrote: Bert, You cannot use a 3D SUMPRODUCT formula - unfortunately, your formulas will need to be complex to deal with your workbook structure. I also want to point out that you really need six weeks for your months, not five, since some months start with one or two days in their "first week" and end up with one or two days in their last week, after four full weeks, - so six, not five, weeks overall. But the best solution to all this is to use just one sheet as your database, and learn how to use filters, subtotals, and/or pivot tables to show and report your data instead of formulas. Of course, figuring that out will take some doing, but it would be a better application. HTH, Bernie MS Excel MVP "Bert T" wrote in message ... Hi! I have a book in Excel 2003 that serves as simple timetable and payroll at the same time, but assignments are scheduled weekly from Mon Sun, which is also the way the employees are paid. That part works perfectly until I have to do the tax analysis, since its breakdown goes by month and year. I have 12 sheets, each for a month in a year and each sheet is made out of 5 weeks. I created them like this because the last days of a month and the beginning of the next can fall on the same week - either on the 5th week of that month or on the 1st of the next. The five weeks also gives the users enough flexibility to organize their weeks as they please. The amount paid each month is figured out from columns C2:C13 (Payments) and D2:D13 (Extras) by their dates in column B2:B13 (this are just a few of the cells in each range, but their similar) with the following formula: =SUMAPRODUCTO(--(B$2:B$13=A19);--(B$2:B$13<=A20);(C$2:C$13+D$2:D$13)) The CATCH is that the prior and/or following sheet might also hold dates from this month. So assuming we're looking at sheet4, the other two are sheet3 and sheet5. My Excel is in Spanish so SUMAPRODUCTO is SUMPRODUCT and Sheet is Hoja. SUMAPRODUCTO(--(Hoja3!B2:B13=A19);--(Hoja3!B2:B13<=A20);(Hoja3!C$2:C$13+Hoja3!D$2:D$13 )) SUMAPRODUCTO(--(Hoja5!B$2:B1$3=A19);--(Hoja5!B$2:B$13<=A$20);(Hoja5!C$2:C$13+Hoja5!D$2:D $13)) A19 and A20 are equivalent to the first and last date of the month and I used them as criteria for the date search. Hoja3 is the prior month and Hoja5 is the following I dont want to have 48 formulas to contend with, but only 12. Is there any way to nest 3 3D SUMPRODUCT formulas in one that sums their product? B21 would hold this formula and its product. Something like SP(SP()+SP()+SP()) or maybe SUM(SP();SP();SP()) Any ideas? I remember seeing this formula somewhere and thats why I ask: =SUMPRODUCT((B1:B10="Europe")+(C1:C10="Y"))-SUMPRODUCT(--(B1:B10="Europe"),--(C1:C10="Y")) I would really appreciate any help. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nest 3 SUMPRODUCT formulas - Possible?
Hahahaha...Ryan, that Holy crap made me laugh so hard!! Anyway, thanks for
the links. They're very helpful. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nest 3 SUMPRODUCT formulas - Possible?
Bernie,
Thanks for the tip about the six weeks. I had thought about it, but since this is a book for my nephew's administration and he doesn't know much about excel, I've tried to make as friendly as possible for him. I just cant believe that Excel cant do what I need it to do :( |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nest 3 SUMPRODUCT formulas - Possible?
Bernie has provide some excellent advice. In the meantime, to sum
Hoja3, Hoja4, and Hoja5 based on the criteria you've laid out, try.... =SUMPRODUCT(--(N(OFFSET(INDIRECT("'Hoja"&{3,4,5}&"'!B2:B13"),ROW (INDIRECT ("2:13"))-2,0,1))=A19),--(N(OFFSET(INDIRECT("'Hoja"&{3,4,5}&"'!B2:B13"), ROW(INDIRECT("2:13"))-2,0,1))<=A20),N(OFFSET(INDIRECT("'Hoja"&{3,4,5}&"' ! C2:C13"),ROW(INDIRECT("2:13"))-2,0,1))+N(OFFSET(INDIRECT("'Hoja"&{3,4,5}& "'!D2:D13"),ROW(INDIRECT("2:13"))-2,0,1))) Hope this helps! http://www.xl-central.com In article , Bert T wrote: Hi! I have a book in Excel 2003 that serves as simple timetable and payroll at the same time, but assignments are scheduled weekly from Mon Sun, which is also the way the employees are paid. That part works perfectly until I have to do the tax analysis, since its breakdown goes by month and year. I have 12 sheets, each for a month in a year and each sheet is made out of 5 weeks. I created them like this because the last days of a month and the beginning of the next can fall on the same week - either on the 5th week of that month or on the 1st of the next. The five weeks also gives the users enough flexibility to organize their weeks as they please. The amount paid each month is figured out from columns C2:C13 (Payments) and D2:D13 (Extras) by their dates in column B2:B13 (this are just a few of the cells in each range, but their similar) with the following formula: =SUMAPRODUCTO(--(B$2:B$13=A19);--(B$2:B$13<=A20);(C$2:C$13+D$2:D$13)) The CATCH is that the prior and/or following sheet might also hold dates from this month. So assuming we're looking at sheet4, the other two are sheet3 and sheet5. My Excel is in Spanish so SUMAPRODUCTO is SUMPRODUCT and Sheet is Hoja. SUMAPRODUCTO(--(Hoja3!B2:B13=A19);--(Hoja3!B2:B13<=A20);(Hoja3!C$2:C$13+Hoja3 !D$2:D$13)) SUMAPRODUCTO(--(Hoja5!B$2:B1$3=A19);--(Hoja5!B$2:B$13<=A$20);(Hoja5!C$2:C$13+ Hoja5!D$2:D$13)) A19 and A20 are equivalent to the first and last date of the month and I used them as criteria for the date search. Hoja3 is the prior month and Hoja5 is the following I dont want to have 48 formulas to contend with, but only 12. Is there any way to nest 3 3D SUMPRODUCT formulas in one that sums their product? B21 would hold this formula and its product. Something like SP(SP()+SP()+SP()) or maybe SUM(SP();SP();SP()) Any ideas? I remember seeing this formula somewhere and thats why I ask: =SUMPRODUCT((B1:B10="Europe")+(C1:C10="Y"))-SUMPRODUCT(--(B1:B10="Europe"),--( C1:C10="Y")) I would really appreciate any help. Thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nest 3 SUMPRODUCT formulas - Possible?
Thanks, Domenic
I will check out your formula. Since I was out of my wits, I redessigned my sheets using 6 weeks instead of 5 and the formula I had come up with, so that all the dates would fall on the same month. But I am going to check your idea to see what happens and to learn from it. Again, thanks "Domenic" wrote: Bernie has provide some excellent advice. In the meantime, to sum Hoja3, Hoja4, and Hoja5 based on the criteria you've laid out, try.... =SUMPRODUCT(--(N(OFFSET(INDIRECT("'Hoja"&{3,4,5}&"'!B2:B13"),ROW (INDIRECT ("2:13"))-2,0,1))=A19),--(N(OFFSET(INDIRECT("'Hoja"&{3,4,5}&"'!B2:B13"), ROW(INDIRECT("2:13"))-2,0,1))<=A20),N(OFFSET(INDIRECT("'Hoja"&{3,4,5}&"' ! C2:C13"),ROW(INDIRECT("2:13"))-2,0,1))+N(OFFSET(INDIRECT("'Hoja"&{3,4,5}& "'!D2:D13"),ROW(INDIRECT("2:13"))-2,0,1))) Hope this helps! http://www.xl-central.com In article , Bert T wrote: Hi! I have a book in Excel 2003 that serves as simple timetable and payroll at the same time, but assignments are scheduled weekly from Mon Γ’¬ Sun, which is also the way the employees are paid. That part works perfectly until I have to do the tax analysis, since its breakdown goes by month and year. I have 12 sheets, each for a month in a year and each sheet is made out of 5 weeks. I created them like this because the last days of a month and the beginning of the next can fall on the same week - either on the 5th week of that month or on the 1st of the next. The five weeks also gives the users enough flexibility to organize their weeks as they please. The amount paid each month is figured out from columns C2:C13 (Payments) and D2:D13 (Extras) by their dates in column B2:B13 (this are just a few of the cells in each range, but their similar) with the following formula: =SUMAPRODUCTO(--(B$2:B$13=A19);--(B$2:B$13<=A20);(C$2:C$13+D$2:D$13)) The CATCH is that the prior and/or following sheet might also hold dates from this month. So assuming we're looking at sheet4, the other two are sheet3 and sheet5. My Excel is in Spanish so SUMAPRODUCTO is SUMPRODUCT and Sheet is Hoja. SUMAPRODUCTO(--(Hoja3!B2:B13=A19);--(Hoja3!B2:B13<=A20);(Hoja3!C$2:C$13+Hoja3 !D$2:D$13)) SUMAPRODUCTO(--(Hoja5!B$2:B1$3=A19);--(Hoja5!B$2:B$13<=A$20);(Hoja5!C$2:C$13+ Hoja5!D$2:D$13)) A19 and A20 are equivalent to the first and last date of the month and I used them as criteria for the date search. Hoja3 is the prior month and Hoja5 is the following I donΓ’¬’t want to have 48 formulas to contend with, but only 12. Is there any way to nest 3 3D SUMPRODUCT formulas in one that sums their product? B21 would hold this formula and its product. Something like SP(SP()+SP()+SP()) or maybe SUM(SP();SP();SP()) Any ideas? I remember seeing this formula somewhere and thatΓ’¬’s why I ask: =SUMPRODUCT((B1:B10="Europe")+(C1:C10="Y"))-SUMPRODUCT(--(B1:B10="Europe"),--( C1:C10="Y")) I would really appreciate any help. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what can I do if I need to nest more than 7 formulas? | Excel Worksheet Functions | |||
multiple variables in sumproduct or if/then formulas | Excel Worksheet Functions | |||
SUMPRODUCT and other formulas combined... | Excel Discussion (Misc queries) | |||
Problems with Sumproduct formulas | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |