SUMIF and 3-D Ranges
 

Will SUMIF work with 3-D ranges? It doesn't appear to me that it does but
maybe I'm doing something wrong.

I have several identical sheets and a summary sheet that is not identical to
the individual sheets. Cell A1 in each detail sheet contains the company
name for that sheet. I have a named range called CompanyNames that is
defined as Sheet1:Sheet10!A1. Cell R40 in each sheet contains the column
total for that sheet. I have a named range called Total_Cash that is defined
as Sheet1:Sheet10!R40. My summary sheet has a column of various items
matching the columns in the detail sheets and then columns for each company
with the company name in row 5 of the column. I want to build the formulas
in each column of my summary sheet so I can simply copy the column and change
the company name to match any new detail sheets that I might add. The
formula that I have tried is as follows:

=Sumif(CompanyNames, B5, Total_Cash)

The formula returns #VALUE!.

I have tried several variations on entering the Criteria but none seem to
work.

Ron In Tulsa wrote...
Will SUMIF work with 3-D ranges? It doesn't appear to me that it does
but
maybe I'm doing something wrong.

No, SUMIF doesn't accept 3D references. Note: 3D references are not
ranges as the term 'range' is used in Excel. Excel range objects fit
entirely within single worksheets. Excel 3D references are nothing more
than syntactic shortcuts that a *few* Excel worksheet functions accept.

. . . I have a named range called CompanyNames that is defined as
Sheet1:Sheet10!A1. . . . I have a named range called Total_Cash that
is defined
as Sheet1:Sheet10!R40. . . . I want to build the formulas in each
column of my
summary sheet so I can simply copy the column and change the company
name
to match any new detail sheets that I might add. The formula that I
have tried
is as follows:

=Sumif(CompanyNames, B5, Total_Cash)
...

Most flexible would be to enter the worksheet names in a single column
range, such as SummarySheet!X1:X10, name that range something like
WSList, and use the formula

=SUMPRODUCT(SUMIF(INDIRECT("'"&WSList&"'!A1"),B5,
INDIRECT("'"&WSList&"'!R40)))


--
hgrove
------------------------------------------------------------------------
hgrove's Profile: http://www.excelforum.com/member.php...o&userid=11432
View this thread: http://www.excelforum.com/showthread...hreadid=319431

Typo!

hgrove wrote...
...
=SUMPRODUCT(SUMIF(INDIRECT("'"&WSList&"'!A1"),B 5,
INDIRECT("'"&WSList&"'!R40)))

Make that


=SUMPRODUCT(SUMIF(INDIRECT("'"&WSList&"'!A1"),B5,
INDIRECT("'"&WSList&"'!R40")))


--
hgrove
------------------------------------------------------------------------
hgrove's Profile: http://www.excelforum.com/member.php...o&userid=11432
View this thread: http://www.excelforum.com/showthread...hreadid=319431