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

Thread: sum if across multiple sheets

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have an array formula that I want to work over multiple sheets but the following formula doesn't work. Am I misstating how excel looks at multiple sheets? I want to look down the same column (A5 to A80) across multiple sheets (sheet names are 1,2,3,4,5,6,7,8,9,10,11,12,13). If the number is 99 in A5 to A80, sum H5:H80 across the 13 sheets and give me the result. Here's the incorrect array formula:
    {=SUM(IF('1:13'!A5:A80=99,'1:13'!H5:H80,""))}



  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,026
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    3-D references cannot be used in array formulas.

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-25 15:26, Aladin Akyurek wrote:
    3-D references cannot be used in array formulas.
    Then how can I set this up to work?

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,026
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-03-25 15:29, paullie d wrote:
    On 2002-03-25 15:26, Aladin Akyurek wrote:
    3-D references cannot be used in array formulas.
    Then how can I set this up to work?
    One way:

    In the main sheet, enter sheet names 1, 2, etc in A from A2 on.

    In B1 enter: 99 [ your condition/criterion ]

    In B2 enter and copy down as far as needed:

    =SUMIF(INDIRECT(A2&"!A5:A80"),B6,INDIRECT(A2&"!H5:H80"))

    Then apply SUM from B2 to the last cell in B.

    Note. There is a recent thread in which a macro has been described, which can list all the sheet names in your WB in the desired range.

    Aladin


Some videos you may like

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
  •