Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with SUMPRODUCT(SUMIF(INDEX formula linking to external fi
Can anyone tell me what's wrong with this formula?
=SUMPRODUCT(SUMIF(INDIRECT([File_Name.xlsm]SheetName&"'"&$A$176:$A$194&"'!j:j"),D3,INDIRECT([File_Name.xlsm]SheetName&"'"&$A$176:$A$194&"'!p:p"))) I am getting #NAME? error. I am using Excel 2007. I have the external file open. I have the Analysis Toolpak Add-Ins. I believe there must be something wrong with where I have the " ' ! but I just can't seem to figure it out. Any ideas would be appreciated! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with SUMPRODUCT(SUMIF(INDEX formula linking to external fi
Here's my best guess:
=SUMPRODUCT(SUMIF(INDIRECT("'[File_Name.xlsm]"&$A$176:$A$194&"'!J:J"),D3,INDIRECT("'[File_Name.xlsm]"&A$176:$A$194&"'!P:P"))) -- Biff Microsoft Excel MVP "gina1221" wrote in message ... Can anyone tell me what's wrong with this formula? =SUMPRODUCT(SUMIF(INDIRECT([File_Name.xlsm]SheetName&"'"&$A$176:$A$194&"'!j:j"),D3,INDIRECT([File_Name.xlsm]SheetName&"'"&$A$176:$A$194&"'!p:p"))) I am getting #NAME? error. I am using Excel 2007. I have the external file open. I have the Analysis Toolpak Add-Ins. I believe there must be something wrong with where I have the " ' ! but I just can't seem to figure it out. Any ideas would be appreciated! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with SUMPRODUCT(SUMIF(INDEX formula linking to external fi
Why don' you tell in words what you are trying to do?
-- __________________________________ HTH Bob "gina1221" wrote in message ... Can anyone tell me what's wrong with this formula? =SUMPRODUCT(SUMIF(INDIRECT([File_Name.xlsm]SheetName&"'"&$A$176:$A$194&"'!j:j"),D3,INDIRECT([File_Name.xlsm]SheetName&"'"&$A$176:$A$194&"'!p:p"))) I am getting #NAME? error. I am using Excel 2007. I have the external file open. I have the Analysis Toolpak Add-Ins. I believe there must be something wrong with where I have the " ' ! but I just can't seem to figure it out. Any ideas would be appreciated! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with SUMPRODUCT(SUMIF(INDEX formula linking to externa
I have an Excel workbook with approximately 65 sheets. I need to sum the
values in column J where it matches column D in an external sheet and return the value from column P for each of the sheets in the workbook. I have the list of sheet names listed on a sheet inside the workbook and that's how I'm trying to use INDIRECT to pull information from all of the sheets instead of just SUMIF and listing each sheet individually as this takes an inordinate amount of time given the number of sheets in use. Thanks, Gina "Bob Phillips" wrote: Why don' you tell in words what you are trying to do? -- __________________________________ HTH Bob "gina1221" wrote in message ... Can anyone tell me what's wrong with this formula? =SUMPRODUCT(SUMIF(INDIRECT([File_Name.xlsm]SheetName&"'"&$A$176:$A$194&"'!j:j"),D3,INDIRECT([File_Name.xlsm]SheetName&"'"&$A$176:$A$194&"'!p:p"))) I am getting #NAME? error. I am using Excel 2007. I have the external file open. I have the Analysis Toolpak Add-Ins. I believe there must be something wrong with where I have the " ' ! but I just can't seem to figure it out. Any ideas would be appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX / MATCH with SUMIF/COUNTIF/SUMPRODUCT Statements? | Excel Worksheet Functions | |||
variable substitution in a formula linking to external workbook | Excel Discussion (Misc queries) | |||
SUMIF and SUMPRODUCT with INDIRECT formula problem | Excel Discussion (Misc queries) | |||
Linking sumif from external database file | Setting up and Configuration of Excel | |||
Linking formula to external spreadsheet | Excel Discussion (Misc queries) |