Excel Trying to use SUMPRODUCT with ranges spanning two worksheets :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Trying to use SUMPRODUCT with ranges spanning two worksheets
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

Genio
Board Regular


Joined: 20 Jul 2002
Posts: 15
Location: Santa Clara, CA

Status: Offline

 Reply with quote  

I am using Excel 2000. I have three ranges and three worksheets, all in one workbook.

The three sheets are named, in order:

Open, Closed, Main

The ranges were named using the Insert, Name, Define method. One of my ranges is named "Case_Create". It is defined as:

=Closed!$Q$1:$Q$1000

I have two other ranges with similar definitions, but different column references.

Here is my issue: My SUMPRODUCT formula on my "Main" sheet works great if I only reference my "Closed" sheet. When I change the range definition to include the "Open" sheet, none of the formulas work. I have looked through everything I can find and cannot find why this won't work.

I am using the accepted format of:

=Open:Closed!$Q$1:$Q$1000

Excel accepts it fine, but the formulas do not work.

Thanks very much for the help.
-Genio

Post Tue Jul 30, 2002 12:58 pm 
 View user's profile Send private message Send e-mail AIM Address

Andrew Poulsom
MrExcel MVP


Joined: 22 Jul 2002
Posts: 8059

Flag: Uk

Status: Offline

 Reply with quote  

Quote from Excel Help:

Guidelines for using 3-D references
You can use 3-D references to refer to cells on other sheets, to define names, and to create formulas by using the following functions: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, and VARPA.


3-D references cannot be used in array formulas.


3-D references cannot be used with the intersection operator (a single space) or in formulas that use implicit intersection.

Post Tue Jul 30, 2002 1:16 pm 
 View user's profile Send private message

Genio
Board Regular


Joined: 20 Jul 2002
Posts: 15
Location: Santa Clara, CA

Status: Offline

 Reply with quote  

Andrew,

I apologize for missing that in the app help section. My bad on that one.

Thanks for the response though. That's an unfortunate limitation in Excel.

Good to know.

Thanks very much,

Genio

Post Tue Jul 30, 2002 1:24 pm 
 View user's profile Send private message Send e-mail AIM Address

Aladin Akyurek
.


Joined: 15 Feb 2002
Posts: 13465
Location: The Hague
Flag: Blank

Status: Offline

 Reply with quote  


quote:

On 2002-07-30 09:24, Genio wrote:
Andrew,

I apologize for missing that in the app help section. My bad on that one.

Thanks for the response though. That's an unfortunate limitation in Excel.

Good to know.

Thanks very much,

Genio



Yes, not all functions can be used in 3d fashion. However, depending on whether such a choice is warrented, you can use THREED from the Morefunc add-in to effect a 3d computation with functions unlisted in the Help file.

Suppose we want total Sheet2!D2:D5 and Sheet3!D2:D5 if Sheet2!C2:C5 and Sheet3!C2:C5 meet the condition specified in A2 in Sheet1.

In B2 enter:

=SUMPRODUCT((THREED(Sheet2:Sheet3!C2:C5)=A2)*(THREED(Sheet2:Sheet3!D2:D5)))

Note. This is not a problem where one would want to use SUMPRODUCT. SUMIF would suffice.

See the figures:

Microsoft Excel - aa3dSumproduct Genio.xls___Running: xl2000 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=

A
B
C
D
1
    
2
a195  
3
    
4
    
Sheet1 

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


Microsoft Excel - aa3dSumproduct Genio.xls___Running: xl2000 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=

B
C
D
E
1
    
2
 a15 
3
 a210 
4
 a115 
5
 a240 
6
    
Sheet2 

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


Microsoft Excel - aa3dSumproduct Genio.xls___Running: xl2000 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=

B
C
D
E
1
    
2
 a125 
3
 a240 
4
 a150 
5
 a270 
6
    
Sheet3 

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


Morefunc is downloadable at:

http://longre.free.fr/english/index.html

Addendum. The ranges of interest must be referred to in native mode, not as names.

Aladin


[ This Message was edited by: Aladin Akyurek on 2002-07-30 10:39 ]

Post Tue Jul 30, 2002 2:31 pm 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.