sumproduct from multiple sheets

G

Guest

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

Roger Govier

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

RagDyeR

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


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

RagDyer

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top