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.