Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Matt
 
Posts: n/a
Default 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?
  #2   Report Post  
Roger Govier
 
Posts: n/a
Default

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?



  #3   Report Post  
RagDyeR
 
Posts: n/a
Default

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?


  #4   Report Post  
RagDyer
 
Posts: n/a
Default

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?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do i auto create multiple files from 1 with multiple sheets Kathy Excel Worksheet Functions 0 July 26th 05 01:23 AM
Copying multiple sheets from one book 2 another and undertake spec Pank Mehta Excel Discussion (Misc queries) 14 March 16th 05 05:41 PM
Multiple sheets selected twa14 Excel Discussion (Misc queries) 2 December 21st 04 12:15 PM
linking multiple sheets to a summary sheet greg g Excel Discussion (Misc queries) 1 December 16th 04 08:43 AM
CountIF across multiple sheets in a workbook Al Excel Worksheet Functions 1 October 29th 04 01:15 PM


All times are GMT +1. The time now is 06:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"