Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Sum or Sumif | Excel Discussion (Misc queries) | |||
SUM(IF( Array to avoid #NUM! values | Excel Worksheet Functions | |||
sumif to add data in multiple sheets | Excel Worksheet Functions | |||
Modify SUMIF and COUNTIF to work with SUBTOTALS | Excel Worksheet Functions |