Trying to use SUMPRODUCT with ranges spanning two worksheets

[Genio]

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

 

[Andrew Poulsom]

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.

 

[Genio]

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

 

[Aladin Akyurek]

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:

aa3dSumproduct Genio.xls
	A	B	C	D
1
2	a1	95
3
4
Sheet1

aa3dSumproduct Genio.xls
	B	C	D	E
1
2		a1	5
3		a2	10
4		a1	15
5		a2	40
6
Sheet2

aa3dSumproduct Genio.xls
	B	C	D	E
1
2		a1	25
3		a2	40
4		a1	50
5		a2	70
6
Sheet3

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