# Thread: Sumif (Excel 97)

1. ## 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. ## 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

3. ## 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.

4. ## 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)

Method 2

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

if your sheets are "named" Sheet1, Sheet2, etc.

#### Posting Permissions

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