sumproduct from multiple sheets

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" <[email protected]> wrote in message
news:[email protected]...
>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!C10:C126)+(Midwest!A10:A126
=1186)*(Midwest!C10:C126)+(West!A10:A126=1186)*(West!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" <[email protected]> wrote in message
news:[email protected]...
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!C10: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" <[email protected]> wrote in message
news:[email protected]...
> First of all, your ranges must be equal.
> Then, try this syntax:
>
>
=SUMPRODUCT((Southwest!A10:A126=1186)*(Southwest!C10:C126)+(Midwest!A10:A126
>
=1186)*(Midwest!C10:C126)+(West!A10:A126=1186)*(West!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" <[email protected]> wrote in message
> news:[email protected]...
> 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!C10: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?
>
>

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

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" <[email protected]> wrote in message
news:[email protected]...
>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!C10:C126)+(Midwest!A10:A126
=1186)*(Midwest!C10:C126)+(West!A10:A126=1186)*(West!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" <[email protected]> wrote in message
news:[email protected]...
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!C10: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" <[email protected]> wrote in message
news:[email protected]...
> First of all, your ranges must be equal.
> Then, try this syntax:
>
>
=SUMPRODUCT((Southwest!A10:A126=1186)*(Southwest!C10:C126)+(Midwest!A10:A126
>
=1186)*(Midwest!C10:C126)+(West!A10:A126=1186)*(West!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" <[email protected]> wrote in message
> news:[email protected]...
> 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!C10: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?
>
>