|
SUMPRODUCT with INDIRECT
I am attempting to replace a SUMIF with a SUMPRODUCT so that I can calc on
workbooks that are not open. I am able to get the SUMPRODUCT to work fine, but when I try to add INDIRECT so that I can build the parms to pass to the SUMPRODUCT, I am getting #REF errors. Once I get this working, I'll try to get the indirect.ext working. I can't seem to find my syntax error. Do you happen to see the problem? SUMPRODUCT without the INDIRECT that is working: =SUMPRODUCT(--('[09122009_Nancy Taylor.xlsm]Weekly Timesheet'!N:N=C6),('[09122009_Nancy Taylor.xlsm]Weekly Timesheet'!L:L)) Trying to work in my INDIRECT statements. The second parm is fine - it's the first one that Excel doesn't like: =SUMPRODUCT((INDIRECT("--('["&$B$2&"_"&D6&".xlsm]weekly timesheet'!N:N=C6")),(INDIRECT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!L:L"))) |
SUMPRODUCT with INDIRECT
The "--" is not part of the cell address. It's there to convert trues and
falses to numbers which Sumproduct can work with. Try this: =SUMPRODUCT(--((INDIRECT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!N:N=C6")),(INDIRECT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!L:L"))) which you could simplify by removing the pair of parentheses around the Indirects Regards, Fred "Nancy Taylor" wrote in message ... I am attempting to replace a SUMIF with a SUMPRODUCT so that I can calc on workbooks that are not open. I am able to get the SUMPRODUCT to work fine, but when I try to add INDIRECT so that I can build the parms to pass to the SUMPRODUCT, I am getting #REF errors. Once I get this working, I'll try to get the indirect.ext working. I can't seem to find my syntax error. Do you happen to see the problem? SUMPRODUCT without the INDIRECT that is working: =SUMPRODUCT(--('[09122009_Nancy Taylor.xlsm]Weekly Timesheet'!N:N=C6),('[09122009_Nancy Taylor.xlsm]Weekly Timesheet'!L:L)) Trying to work in my INDIRECT statements. The second parm is fine - it's the first one that Excel doesn't like: =SUMPRODUCT((INDIRECT("--('["&$B$2&"_"&D6&".xlsm]weekly timesheet'!N:N=C6")),(INDIRECT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!L:L"))) |
SUMPRODUCT with INDIRECT
Hi Fred,
Thanks for the reply. I tried this but I still get the #REF! error. I'll keep trying! Nancy "Fred Smith" wrote: The "--" is not part of the cell address. It's there to convert trues and falses to numbers which Sumproduct can work with. Try this: =SUMPRODUCT(--((INDIRECT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!N:N=C6")),(INDIRECT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!L:L"))) which you could simplify by removing the pair of parentheses around the Indirects Regards, Fred "Nancy Taylor" wrote in message ... I am attempting to replace a SUMIF with a SUMPRODUCT so that I can calc on workbooks that are not open. I am able to get the SUMPRODUCT to work fine, but when I try to add INDIRECT so that I can build the parms to pass to the SUMPRODUCT, I am getting #REF errors. Once I get this working, I'll try to get the indirect.ext working. I can't seem to find my syntax error. Do you happen to see the problem? SUMPRODUCT without the INDIRECT that is working: =SUMPRODUCT(--('[09122009_Nancy Taylor.xlsm]Weekly Timesheet'!N:N=C6),('[09122009_Nancy Taylor.xlsm]Weekly Timesheet'!L:L)) Trying to work in my INDIRECT statements. The second parm is fine - it's the first one that Excel doesn't like: =SUMPRODUCT((INDIRECT("--('["&$B$2&"_"&D6&".xlsm]weekly timesheet'!N:N=C6")),(INDIRECT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!L:L"))) |
SUMPRODUCT with INDIRECT
Sorry, I didn't look closing enough. Indirect is used to create the range.
The rest of the formula needs to be outside of the Indirect function. Try this: =SUMPRODUCT(--(INDIRECT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!N:N")=C6),INDIRECT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!L:L")) If you're still getting a #REF error, you need to check B2 and D6. Try testing the Indirect formula to make sure it is creating the proper address for you. Regards, Fred. "Nancy Taylor" wrote in message ... Hi Fred, Thanks for the reply. I tried this but I still get the #REF! error. I'll keep trying! Nancy "Fred Smith" wrote: The "--" is not part of the cell address. It's there to convert trues and falses to numbers which Sumproduct can work with. Try this: =SUMPRODUCT(--((INDIRECT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!N:N=C6")),(INDIRECT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!L:L"))) which you could simplify by removing the pair of parentheses around the Indirects Regards, Fred "Nancy Taylor" wrote in message ... I am attempting to replace a SUMIF with a SUMPRODUCT so that I can calc on workbooks that are not open. I am able to get the SUMPRODUCT to work fine, but when I try to add INDIRECT so that I can build the parms to pass to the SUMPRODUCT, I am getting #REF errors. Once I get this working, I'll try to get the indirect.ext working. I can't seem to find my syntax error. Do you happen to see the problem? SUMPRODUCT without the INDIRECT that is working: =SUMPRODUCT(--('[09122009_Nancy Taylor.xlsm]Weekly Timesheet'!N:N=C6),('[09122009_Nancy Taylor.xlsm]Weekly Timesheet'!L:L)) Trying to work in my INDIRECT statements. The second parm is fine - it's the first one that Excel doesn't like: =SUMPRODUCT((INDIRECT("--('["&$B$2&"_"&D6&".xlsm]weekly timesheet'!N:N=C6")),(INDIRECT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!L:L"))) |
SUMPRODUCT with INDIRECT
Hi Fred,
I kept making adjustments and realized that too. Once I got the =C6 out of the way, it worked just fine. Now, on to trying to get the Indirect.ext to work so that I don't have to open so many spreadsheets to get all of this to work! Thanks so much for your help! Nancy "Fred Smith" wrote: Sorry, I didn't look closing enough. Indirect is used to create the range. The rest of the formula needs to be outside of the Indirect function. Try this: =SUMPRODUCT(--(INDIRECT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!N:N")=C6),INDIRECT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!L:L")) If you're still getting a #REF error, you need to check B2 and D6. Try testing the Indirect formula to make sure it is creating the proper address for you. Regards, Fred. "Nancy Taylor" wrote in message ... Hi Fred, Thanks for the reply. I tried this but I still get the #REF! error. I'll keep trying! Nancy "Fred Smith" wrote: The "--" is not part of the cell address. It's there to convert trues and falses to numbers which Sumproduct can work with. Try this: =SUMPRODUCT(--((INDIRECT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!N:N=C6")),(INDIRECT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!L:L"))) which you could simplify by removing the pair of parentheses around the Indirects Regards, Fred "Nancy Taylor" wrote in message ... I am attempting to replace a SUMIF with a SUMPRODUCT so that I can calc on workbooks that are not open. I am able to get the SUMPRODUCT to work fine, but when I try to add INDIRECT so that I can build the parms to pass to the SUMPRODUCT, I am getting #REF errors. Once I get this working, I'll try to get the indirect.ext working. I can't seem to find my syntax error. Do you happen to see the problem? SUMPRODUCT without the INDIRECT that is working: =SUMPRODUCT(--('[09122009_Nancy Taylor.xlsm]Weekly Timesheet'!N:N=C6),('[09122009_Nancy Taylor.xlsm]Weekly Timesheet'!L:L)) Trying to work in my INDIRECT statements. The second parm is fine - it's the first one that Excel doesn't like: =SUMPRODUCT((INDIRECT("--('["&$B$2&"_"&D6&".xlsm]weekly timesheet'!N:N=C6")),(INDIRECT("'["&$B$2&"_"&D6&".xlsm]weekly timesheet'!L:L"))) |
| All times are GMT +1. The time now is 02:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com