sumproduct from multiple sheets
 

I am trying to write a sumproduct formula to add 4 numbers off 4 different
worksheets within the same file. Here is my formula:

=SUMPRODUCT(--(Southwest!A10:A126=1186),--(Midwest!A10:A124=1186),--(West!A10:A120=1186),--(East!A10:A118=1186),(Southwest!C10:C126+Midwest!C 10:C124+West!C10:C120+East!C10:C118))

All of the four worksheets have 1186 as a value, and I'm getting a #value#
error when I hit enter. Is it possible to have a sumproduct function using 4
different sheets, or does it have to be in the same sheet?

Hi Matt
I have never tried SUMPRODUCT with multiple sheets, but I think your problem
is the size of the ranges.
They have to be identical in size for there to be corresponding True/False
responses to be multiplied.
I think you would need to treat each as separate SUMPRODUCT equations and
add them together.
=SUMPRODUCT(--(Southwest!A10:A126=1186),--(Southwest!C10:C126))+SUMPRODUCT(--(Midwest!A10:A124=1186),
--(Midwest!C10:C124))+ etc.

--
Regards

Roger Govier


"Matt" wrote in message
...
I am trying to write a sumproduct formula to add 4 numbers off 4 different
worksheets within the same file. Here is my formula:

=SUMPRODUCT(--(Southwest!A10:A126=1186),--(Midwest!A10:A124=1186),--(West!A10:A120=1186),--(East!A10:A118=1186),(C+Midwest!C10:C124+West!C10: C120+East!C10:C118))

All of the four worksheets have 1186 as a value, and I'm getting a #value#
error when I hit enter. Is it possible to have a sumproduct function
using 4
different sheets, or does it have to be in the same sheet?

First of all, your ranges must be equal.
Then, try this syntax:

=SUMPRODUCT((Southwest!A10:A126=1186)*(Southwest!C 10:C126)+(Midwest!A10:A126
=1186)*(Midwest!C10:C126)+(West!A10:A126=1186)*(We st!C10:C126)+(East!A10:A12
6=1186)*(East!C10:C126))

If you *cannot* equalize your ranges, for some reason, try this:

=SUMIF(Southwest!A10:A126,1186,Southwest!C10:C126) +SUMIF(Midwest!A10:A124,11
86,Midwest!C10:C124)+SUMIF(West!A10:A120,1186,West !C10:C120)+SUMIF(East!A10:
A118,1186,East!C10:C118)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Matt" wrote in message
...
I am trying to write a sumproduct formula to add 4 numbers off 4 different
worksheets within the same file. Here is my formula:

=SUMPRODUCT(--(Southwest!A10:A126=1186),--(Midwest!A10:A124=1186),--(West!A1
0:A120=1186),--(East!A10:A118=1186),(Southwest!C10:C126+Midwest!C 10:C124+Wes
t!C10:C120+East!C10:C118))

All of the four worksheets have 1186 as a value, and I'm getting a #value#
error when I hit enter. Is it possible to have a sumproduct function using
4
different sheets, or does it have to be in the same sheet?

Just realized that I probably misinterpreted the actual purpose of your
formula.

I jumped to the conclusion that you intended to match the criteria on
individual pages and sum the matches.

Your formula *will* work to match the criteria on *all* 4 pages, and then
sum the matches *IF You Just Equalize All Your Ranges*!


--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"RagDyeR" wrote in message
...
First of all, your ranges must be equal.
Then, try this syntax:


=SUMPRODUCT((Southwest!A10:A126=1186)*(Southwest!C 10:C126)+(Midwest!A10:A126

=1186)*(Midwest!C10:C126)+(West!A10:A126=1186)*(We st!C10:C126)+(East!A10:A12
6=1186)*(East!C10:C126))

If you *cannot* equalize your ranges, for some reason, try this:


=SUMIF(Southwest!A10:A126,1186,Southwest!C10:C126) +SUMIF(Midwest!A10:A124,11

86,Midwest!C10:C124)+SUMIF(West!A10:A120,1186,West !C10:C120)+SUMIF(East!A10:
A118,1186,East!C10:C118)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Matt" wrote in message
...
I am trying to write a sumproduct formula to add 4 numbers off 4 different
worksheets within the same file. Here is my formula:


=SUMPRODUCT(--(Southwest!A10:A126=1186),--(Midwest!A10:A124=1186),--(West!A1

0:A120=1186),--(East!A10:A118=1186),(Southwest!C10:C126+Midwest!C 10:C124+Wes
t!C10:C120+East!C10:C118))

All of the four worksheets have 1186 as a value, and I'm getting a #value#
error when I hit enter. Is it possible to have a sumproduct function
using
4
different sheets, or does it have to be in the same sheet?