SumProduct for multiple WORKBOOKS

[chanbrig]

Ok,

I have read literally every thread on the use of sumproduct fo
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 sam
information b/c it is a template sent to sales people. Let's say th
names are Brown.xls, Curry.xls, & Matthews.xls

*3-* On worksheet entitled "Monthly Report" cell range C15-C24 needs t
be summed IF it contains at certain value (let's say "2").

*4- *The sumproduct formula is being placed in a summary Workboo
entitled "Cumulative Monthly Report"

*What is the complete SUMPRODUCT formula for this?*

PLEASE HELP!:eek

 

[Bob Phillips]

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]

Bob:

Should I create one "master" spreadsheet and input the sumproduct
formula for each workbook in a separate field and sum the totals?

 

[Harlan Grove]

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*.

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)

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*.

 

[chanbrig]

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?

 

[Harlan Grove]

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]

Harlan:

I have worked on this for hours and it is still not working. I copie
the syntax you gave exactly and replaced names accordingly and i
returns errors.

Both versions (short and long formula) gave me the mesage "the formul
you typed contains an error".

Here it is again...

Short version:
=(SUMPRODUCT(--('S:\June\[Metzger.xls]MONTHL
REPORTS'!$C$15:$C$24=2)),+SUMPRODUCT(--('S:\June\[Opp.xls]MONTHL
REPORTS'!$C$15:$C$24=2)),+SUMPRODUCT(--('S:\June\[Burke.xls]MONTHL
REPORTS'!$C$15:$C$24=2)))*2


Long version:
=SUMPRODUCT(--('S:\ENTERPRISE SALES SOLUTIONS\Opportunities & Risk
Reports\OIS ESS Monthly Reports\June\[Metzger.xls]MONTHL
REPORTS'!$C$15:$C$24=2),
'S:\ENTERPRISE SALES SOLUTIONS\Opportunities & Risks Reports\OIS ES
Monthly Reports\June\[Metzger.xls]MONTHL
REPORTS'!$C$15:$C$24)+SUMPRODUCT(--(‘S:\ENTERPRISE SALE
SOLUTIONS\Opportunities & Risks Reports\OIS ESS Monthl
Reports\June\[Opp.xls]MONTHLY REPORTS’!$C$15:$C$24=2),'S:\ENTERPRIS
SALES SOLUTIONS\Opportunities & Risks Reports\OIS ESS Monthl
Reports\June\[Opp.xls]MONTHL
REPORTS'!$C$15:$C$24)+SUMPRODUCT(--('S:\ENTERPRISE SALE
SOLUTIONS\Opportunities & Risks Reports\OIS ESS Monthl
Reports\June\[Burke.xls]MONTHLY REPORTS’!$C$15:$C$24=2),'S:\ENTERPRIS
SALES SOLUTIONS\Opportunities & Risks Reports\OIS ESS Monthl
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!

chanbrig wrote...

Yes, the workbooks will be closed.

I tried a formula but I am getting a naming error. Please take 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* th
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]MONTHL
REPORTS'!$C$15:$C$24=2)))*

 

[Harlan Grove]

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]

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 th
formula?

THANK YOU THANK YOU THANK YOU!

chanbrig wrote...
....

Both versions (short and long formula) gave me the mesage "th 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]MONTHL

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 Exce

say
there's an error