Trying to use SUMPRODUCT with ranges spanning two worksheets

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Trying to use SUMPRODUCT with ranges spanning two worksheets

  1. #1
    New Member
    Join Date
    Jul 2002
    Location
    Santa Clara, CA
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    New Member
    Join Date
    Jul 2002
    Location
    Santa Clara, CA
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,465
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com