SUMIF and 3-D Ranges

[Guest]

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.

 

[hgrove]

Ron In Tulsa wrote...

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

No, SUMIF doesn't accept 3D references. Note: 3D references are no
ranges as the term 'range' is used in Excel. Excel range objects fi
entirely within single worksheets. Excel 3D references are nothing mor
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 tha is defined
as Sheet1:Sheet10!R40. . . . I want to build the formulas in eac column of my
summary sheet so I can simply copy the column and change the compan name
to match any new detail sheets that I might add. The formula that have tried
is as follows:

=Sumif(CompanyNames, B5, Total_Cash)

...

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

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

 

[hgrove]

Typo!

hgrove wrote...
...

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

Make that


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