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

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

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

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

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

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

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

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