SumProduct for multiple WORKBOOKS

[chanbrig]

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!

[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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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)

"chanbrig" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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)

"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> 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?

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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 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!

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

[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 the formula?

THANK YOU THANK YOU THANK YOU!

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?