| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct for multiple WORKBOOKS
Ok, I have read literally every thread on the use of sumproduct for multiple workbooks BUT still cannot get it to work for my project. Here are the details: *1-* I have 3 separate WORKBOOKS *2-* Each workbook has a different file name but contains the same information b/c it is a template sent to sales people. Let's say the names are Brown.xls, Curry.xls, & Matthews.xls *3-* On worksheet entitled "Monthly Report" cell range C15-C24 needs to be summed IF it contains at certain value (let's say "2"). *4- *The sumproduct formula is being placed in a summary Workbook entitled "Cumulative Monthly Report" *What is the complete SUMPRODUCT formula for this?* PLEASE HELP!  -- chanbrig ------------------------------------------------------------------------ chanbrig's Profile: http://www.excelforum.com/member.php...o&userid=36071 View this thread: http://www.excelforum.com/showthread...hreadid=558587 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct for multiple WORKBOOKS
3D is bad enough, 4D no chance I think.
You will need you would need 3 separate formulae, and add each of those. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "chanbrig" wrote in message ... Ok, I have read literally every thread on the use of sumproduct for multiple workbooks BUT still cannot get it to work for my project. Here are the details: *1-* I have 3 separate WORKBOOKS *2-* Each workbook has a different file name but contains the same information b/c it is a template sent to sales people. Let's say the names are Brown.xls, Curry.xls, & Matthews.xls *3-* On worksheet entitled "Monthly Report" cell range C15-C24 needs to be summed IF it contains at certain value (let's say "2"). *4- *The sumproduct formula is being placed in a summary Workbook entitled "Cumulative Monthly Report" *What is the complete SUMPRODUCT formula for this?* PLEASE HELP! -- chanbrig ------------------------------------------------------------------------ chanbrig's Profile: http://www.excelforum.com/member.php...o&userid=36071 View this thread: http://www.excelforum.com/showthread...hreadid=558587 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct for multiple WORKBOOKS
Bob: Should I create one "master" spreadsheet and input the sumproduct formula for each workbook in a separate field and sum the totals? -- chanbrig ------------------------------------------------------------------------ chanbrig's Profile: http://www.excelforum.com/member.php...o&userid=36071 View this thread: http://www.excelforum.com/showthread...hreadid=558587 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct for multiple WORKBOOKS
Bob Phillips wrote...
3D is bad enough, 4D no chance I think. You will need you would need 3 separate formulae, and add each of those. .... 3D and 4D would work the same way. If there's only one criterion *AND* the files were all open in memory, use =SUM(SUMIF(INDIRECT("'["&{"Brown";"Curry";"Matthews"} &".xls]Monthly Report'!C15:C24"),2)) If these other workbooks weren't open, 3 separate SUMPRODUCT *function* calls would be needed, but they could all be put into a single *formula*. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct for multiple WORKBOOKS
NO, I would just use
=SUMPRODUCT(against workbook1)+ SUMPRODUCT(against workbook2)+ SUMPRODUCT(against workbook3) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "chanbrig" wrote in message ... Bob: Should I create one "master" spreadsheet and input the sumproduct formula for each workbook in a separate field and sum the totals? -- chanbrig ------------------------------------------------------------------------ chanbrig's Profile: http://www.excelforum.com/member.php...o&userid=36071 View this thread: http://www.excelforum.com/showthread...hreadid=558587 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct for multiple WORKBOOKS
Because SUMPRODUCT was specified I assumed the workbooks would be closed.
Maybe a bit of an suumption on my part, maybe not. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Harlan Grove" wrote in message ups.com... Bob Phillips wrote... 3D is bad enough, 4D no chance I think. You will need you would need 3 separate formulae, and add each of those. ... 3D and 4D would work the same way. If there's only one criterion *AND* the files were all open in memory, use =SUM(SUMIF(INDIRECT("'["&{"Brown";"Curry";"Matthews"} &".xls]Monthly Report'!C15:C24"),2)) If these other workbooks weren't open, 3 separate SUMPRODUCT *function* calls would be needed, but they could all be put into a single *formula*. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct for multiple WORKBOOKS
Yes, the workbooks will be closed. I tried a formula but I am getting a naming error. Please take a look: =SUMPRODUCT(--([Brown.xls]MONTHLY REPORTS!$C$15:$C$24=2),[Brown.xls]MONTHLY REPORTS!$C$15:C$24)+ SUMPRODUCT(--([Curry.xls]MONTHLY REPORTS!$C$15:$C$24=2),[Brown.xls]MONTHLY REPORTS!$C$15:C$24)+ SUMPRODUCT(--([Matthews.xls]MONTHLY REPORTS!$C$15:$C$24=2),[Brown.xls]MONTHLY REPORTS!$C$15:C$24) I guess I really don't understand the SUMProduct formula. Please help. What should the complete formula look like? Bob Phillips Wrote: NO, I would just use =SUMPRODUCT(against workbook1)+ SUMPRODUCT(against workbook2)+ SUMPRODUCT(against workbook3) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "chanbrig" wrote in message ... Bob: Should I create one "master" spreadsheet and input the sumproduct formula for each workbook in a separate field and sum the totals? -- chanbrig ------------------------------------------------------------------------ chanbrig's Profile: http://www.excelforum.com/member.php...o&userid=36071 View this thread: http://www.excelforum.com/showthread...hreadid=558587 -- chanbrig ------------------------------------------------------------------------ chanbrig's Profile: http://www.excelforum.com/member.php...o&userid=36071 View this thread: http://www.excelforum.com/showthread...hreadid=558587 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct for multiple WORKBOOKS
chanbrig wrote...
Yes, the workbooks will be closed. I tried a formula but I am getting a naming error. Please take a look: =SUMPRODUCT(--([Brown.xls]MONTHLY REPORTS!$C$15:$C$24=2),[Brown.xls]MONTHLY REPORTS!$C$15:C$24)+ SUMPRODUCT(--([Curry.xls]MONTHLY REPORTS!$C$15:$C$24=2),[Brown.xls]MONTHLY REPORTS!$C$15:C$24)+ SUMPRODUCT(--([Matthews.xls]MONTHLY REPORTS!$C$15:$C$24=2),[Brown.xls]MONTHLY REPORTS!$C$15:C$24) .... If you have any spaces anywhere in the workbook name *OR* the worksheet name, then you must enclose the combined workbook-worksheet name in single quotes. That's why the formula above has syntax errors. Also, if the files would be closed, you need to include their drive/directory paths. Finally, as written, your formula appears to be fubar - you're always summing up values from Brown.xls. =SUMPRODUCT(--('D:\dir\[Brown.xls]MONTHLY REPORTS'!$C$15:$C$24=2), 'D:\dir\[Brown.xls]MONTHLY REPORTS'!$C$15:C$24)+ SUMPRODUCT(--('D:\dir\[Curry.xls]MONTHLY REPORTS'!$C$15:$C$24=2), 'D:\dir\[Curry.xls]MONTHLY REPORTS!$C$15:C$24)+ SUMPRODUCT(--('D:\dir\[Matthews.xls]MONTHLY REPORTS'!$C$15:$C$24=2), 'D:\dir\[Matthews.xls]MONTHLY REPORTS'!$C$15:C$24) If this is really what you want, you could simplify it to =(SUMPRODUCT(--('D:\dir\[Brown.xls]MONTHLY REPORTS'!$C$15:$C$24=2)), +SUMPRODUCT(--('D:\dir\[Curry.xls]MONTHLY REPORTS'!$C$15:$C$24=2)), +SUMPRODUCT(--('D:\dir\[Matthews.xls]MONTHLY REPORTS'!$C$15:$C$24=2)))*2 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct for multiple WORKBOOKS
Harlan: I have worked on this for hours and it is still not working. I copied the syntax you gave exactly and replaced names accordingly and it returns errors. Both versions (short and long formula) gave me the mesage "the formula you typed contains an error". Here it is again... Short version: =(SUMPRODUCT(--('S:\June\[Metzger.xls]MONTHLY REPORTS'!$C$15:$C$24=2)),+SUMPRODUCT(--('S:\June\[Opp.xls]MONTHLY REPORTS'!$C$15:$C$24=2)),+SUMPRODUCT(--('S:\June\[Burke.xls]MONTHLY REPORTS'!$C$15:$C$24=2)))*2 Long version: =SUMPRODUCT(--('S:\ENTERPRISE SALES SOLUTIONS\Opportunities & Risks Reports\OIS ESS Monthly Reports\June\[Metzger.xls]MONTHLY REPORTS'!$C$15:$C$24=2), 'S:\ENTERPRISE SALES SOLUTIONS\Opportunities & Risks Reports\OIS ESS Monthly Reports\June\[Metzger.xls]MONTHLY REPORTS'!$C$15:$C$24)+SUMPRODUCT(--(‘S:\ENTERPRISE SALES SOLUTIONS\Opportunities & Risks Reports\OIS ESS Monthly Reports\June\[Opp.xls]MONTHLY REPORTS’!$C$15:$C$24=2),'S:\ENTERPRISE SALES SOLUTIONS\Opportunities & Risks Reports\OIS ESS Monthly Reports\June\[Opp.xls]MONTHLY REPORTS'!$C$15:$C$24)+SUMPRODUCT(--('S:\ENTERPRISE SALES SOLUTIONS\Opportunities & Risks Reports\OIS ESS Monthly Reports\June\[Burke.xls]MONTHLY REPORTS’!$C$15:$C$24=2),'S:\ENTERPRISE SALES SOLUTIONS\Opportunities & Risks Reports\OIS ESS Monthly Reports\June\[Burke.xls]MONTHLY REPORTS'!$C$15:$C$24) I will check back tomorrow. It has been a looooong day. Thanks for your help! Harlan Grove Wrote: chanbrig wrote... Yes, the workbooks will be closed. I tried a formula but I am getting a naming error. Please take a look: =SUMPRODUCT(--([Brown.xls]MONTHLY REPORTS!$C$15:$C$24=2),[Brown.xls]MONTHLY REPORTS!$C$15:C$24)+ SUMPRODUCT(--([Curry.xls]MONTHLY REPORTS!$C$15:$C$24=2),[Brown.xls]MONTHLY REPORTS!$C$15:C$24)+ SUMPRODUCT(--([Matthews.xls]MONTHLY REPORTS!$C$15:$C$24=2),[Brown.xls]MONTHLY REPORTS!$C$15:C$24) .... If you have any spaces anywhere in the workbook name *OR* the worksheet name, then you must enclose the combined workbook-worksheet name in single quotes. That's why the formula above has syntax errors. Also, if the files would be closed, you need to include their drive/directory paths. Finally, as written, your formula appears to be fubar - you're always summing up values from Brown.xls. =SUMPRODUCT(--('D:\dir\[Brown.xls]MONTHLY REPORTS'!$C$15:$C$24=2), 'D:\dir\[Brown.xls]MONTHLY REPORTS'!$C$15:C$24)+ SUMPRODUCT(--('D:\dir\[Curry.xls]MONTHLY REPORTS'!$C$15:$C$24=2), 'D:\dir\[Curry.xls]MONTHLY REPORTS!$C$15:C$24)+ SUMPRODUCT(--('D:\dir\[Matthews.xls]MONTHLY REPORTS'!$C$15:$C$24=2), 'D:\dir\[Matthews.xls]MONTHLY REPORTS'!$C$15:C$24) If this is really what you want, you could simplify it to =(SUMPRODUCT(--('D:\dir\[Brown.xls]MONTHLY REPORTS'!$C$15:$C$24=2)), +SUMPRODUCT(--('D:\dir\[Curry.xls]MONTHLY REPORTS'!$C$15:$C$24=2)), +SUMPRODUCT(--('D:\dir\[Matthews.xls]MONTHLY REPORTS'!$C$15:$C$24=2)))*2 -- chanbrig ------------------------------------------------------------------------ chanbrig's Profile: http://www.excelforum.com/member.php...o&userid=36071 View this thread: http://www.excelforum.com/showthread...hreadid=558587 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct for multiple WORKBOOKS
chanbrig wrote...
.... Both versions (short and long formula) gave me the mesage "the formula you typed contains an error". Here it is again... Short version: =(SUMPRODUCT(--('S:\June\[Metzger.xls]MONTHLY REPORTS'!$C$15:$C$24=2)),+SUMPRODUCT(--('S:\June\[Opp.xls]MONTHLY REPORTS'!$C$15:$C$24=2)),+SUMPRODUCT(--('S:\June\[Burke.xls]MONTHLY REPORTS'!$C$15:$C$24=2)))*2 Oops! My fault. There should be no commas, so this should be =(SUMPRODUCT(--('S:\June\[Metzger.xls]MONTHLY REPORTS'!$C$15:$C$24=2)) +SUMPRODUCT(--('S:\June\[Opp.xls]MONTHLY REPORTS'!$C$15:$C$24=2)) +SUMPRODUCT(--('S:\June\[Burke.xls]MONTHLY REPORTS'!$C$15:$C$24=2)))*2 Long version: .... From what I can see, the long version should work. Where does Excel say there's an error? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct for multiple WORKBOOKS
Harlan! I think I love you! ;) THAT WORKED! I am using the short version. When I removed the commas, it began calculating! Just for my understanding. What does the *2 mean at the end of the formula? THANK YOU THANK YOU THANK YOU! Harlan Grove Wrote: chanbrig wrote... .... Both versions (short and long formula) gave me the mesage "the formula you typed contains an error". Here it is again... Short version: =(SUMPRODUCT(--('S:\June\[Metzger.xls]MONTHLY REPORTS'!$C$15:$C$24=2)),+SUMPRODUCT(--('S:\June\[Opp.xls]MONTHLY REPORTS'!$C$15:$C$24=2)),+SUMPRODUCT(--('S:\June\[Burke.xls]MONTHLY REPORTS'!$C$15:$C$24=2)))*2 Oops! My fault. There should be no commas, so this should be =(SUMPRODUCT(--('S:\June\[Metzger.xls]MONTHLY REPORTS'!$C$15:$C$24=2)) +SUMPRODUCT(--('S:\June\[Opp.xls]MONTHLY REPORTS'!$C$15:$C$24=2)) +SUMPRODUCT(--('S:\June\[Burke.xls]MONTHLY REPORTS'!$C$15:$C$24=2)))*2 Long version: .... From what I can see, the long version should work. Where does Excel say there's an error? -- chanbrig ------------------------------------------------------------------------ chanbrig's Profile: http://www.excelforum.com/member.php...o&userid=36071 View this thread: http://www.excelforum.com/showthread...hreadid=558587 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Linking Groups of cells between workbooks | Excel Discussion (Misc queries) | |||
| Sumproduct calculation between two workbooks | Excel Worksheet Functions | |||
| Links auto update on some workbooks but not others | Excel Worksheet Functions | |||
| Workbooks...I'll try this again... | Excel Discussion (Misc queries) | |||
| adding two sumproduct formulas together | Excel Worksheet Functions | |||
Linear Mode
