SUMPRODUCT with INDIRECT

[Nancy Taylor]

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")))

 

[Fred Smith]

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]

Hi Fred,

Thanks for the reply. I tried this but I still get the #REF! error. I'll
keep trying!

Nancy

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

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")))

 

[Fred Smith]

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.

Hi Fred,

Thanks for the reply. I tried this but I still get the #REF! error. I'll
keep trying!

Nancy

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

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")))

 

[Nancy Taylor]

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

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.

Hi Fred,

Thanks for the reply. I tried this but I still get the #REF! error. I'll
keep trying!

Nancy

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

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")))