Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Location
    Prince George, Br. Columbia, Canada
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sumif (Excel 97)

    I am trying to sum a single cell in multiple sheets based on the contents of another cell in the same series of sheets...eg. the total I want to add is in A2 and the criteria is in A1. In sheets 1 to 9 there can be 3 different entries in A1(A, B or C). I tried =sumif(sheet1:sheet9!A1,"A",sheet1:sheet9) in cell a1 of a summary sheet followed by the criteria B and C in A2 and A3. I'm getting a #vallue error.

    anyone see where this is wrong or doesn't the sumif work on an array of sheets?

    Thanks for any tips

    Larry

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Sumif (Excel 97)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> LAB

    Just this morning I answered a similar question. Please check this post <!post=Sumif CountIf,277608>Sumif CountIf<!/post> and if this does not help, please post a reply.

    There is also an attached workbook, with an example.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif (Excel 97)

    SUMIF is not one of the functions that works with 3-D references - see the Online Help.
    <hr>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.<hr>
    You will need to find another way to structure your data - possibly by bringing the key information through to a Totals worksheet.
    Gre

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif (Excel 97)

    Method 1

    Insert two new sheets, name them First and Last, place all relevant worksheets between these to, and in A2 enter:

    =COUNTIF.3D(First:Last!A1,A1)

    which requires the morefunc.xll add-in, downloadable from: http://longre.free.fr/english/index.html

    Method 2

    =SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT ("1:9"))&"'!A1"),A1))

    if your sheets are "named" Sheet1, Sheet2, etc.
    Microsoft MVP - Excel

Posting Permissions

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