SUMPRODUCT & INDIRECT Cell to Sheet Name

[shemayisroel]

Hi,

Attempting to use SUMPRODUCT with INDIRECT to link a cell reference to a sheet name.

Basic structure is below but throws back a #REF! error. Any suggestions on how to clean it up and yield the desired result?

=SUMPRODUCT(--(INDIRECT(cellref&"!Range1")=Criteria1),--(INDIRECT(cellref&"!Range2")=Criteria2),INDIRECT(cellref&"!SumRange"))

Thanks

 

[Aladin Akyurek]

Hi,

Attempting to use SUMPRODUCT with INDIRECT to link a cell reference to a sheet name.

Basic structure is below but throws back a #REF! error. Any suggestions on how to clean it up and yield the desired result?

=SUMPRODUCT(--(INDIRECT(cellref&"!Range1")=Criteria1),--(INDIRECT(cellref&"!Range2")=Criteria2),INDIRECT(cellref&"!SumRange"))

Thanks

You want something like...

=SUMPRODUCT(
--(INDIRECT("'"&A2&"'!E2:E10")=Criteria1),
--(INDIRECT("'"&A2&"'!F2:F10")=Criteria2),
INDIRECT("'"&A2&"'!G2:G10"))

where G2:G10 stands for the sum range.

 

[T. Valko]

Hi,

Attempting to use SUMPRODUCT with INDIRECT to link a cell reference to a sheet name.

Basic structure is below but throws back a #REF! error. Any suggestions on how to clean it up and yield the desired result?

=SUMPRODUCT(--(INDIRECT(cellref&"!Range1")=Criteria1),--(INDIRECT(cellref&"!Range2")=Criteria2),INDIRECT(cellref&"!SumRange"))

Thanks

If your sheet name contains space characters or numbers then you have to put single quotes around the sheet name:

...INDIRECT("'"&cellref&"'!Range")...

Here it is in "exploded" view:

...INDIRECT(" ' " & cellref & " ' ! Range")...

Also, if the ranges are dynamic ranges defined using functions like OFFSET then INDIRECT won't work.

 

[shemayisroel]

Aladin Akyurek & T Valko - thanks very much for a quick response.

I'll give it a go within the WB and report back if I encounter any issues.

 

[shemayisroel]

Just tried it in the WB and retruned #REF! error.

Here it is

=SUMPRODUCT(--(INDIRECT(""&P$3&"'!$A$1:$A$:500")=$C7),--(INDIRECT(""&P$3&"'!$L$1:$L$500")=P$4),INDIRECT(""&P$3&"'!$F$1:$F$500"))

P3 = sheetname = contains a number than work like "3sample"

3sample sheet does exist and is in lower case

Any ideas...

 

[T. Valko]

Just tried it in the WB and retruned #REF! error.

Here it is

=SUMPRODUCT(--(INDIRECT(""&P$3&"'!$A$1:$A$:500")=$C7),--(INDIRECT(""&P$3&"'!$L$1:$L$500")=P$4),INDIRECT(""&P$3&"'!$F$1:$F$500"))

P3 = sheetname = contains a number than work like "3sample"

3sample sheet does exist and is in lower case

Any ideas...

You have the quotes wrong at the beginning.

Try it like this...

=SUMPRODUCT(--(INDIRECT("'"&P$3&"'!A1:A500")=$C7),--(INDIRECT("'"&P$3&"'!L1:L500")=P$4),INDIRECT("'"&P$3&"'!F1:F500"))

Also, notice that I removed the $ signs from some of the ranges.

Since these ranges are being defined within the INDIRECT function they are TEXT strings and as such they will never change if the formula is copied to other locations and they will automatically be evaluated as absolute references.

 

[T. Valko]

You have the quotes wrong at the beginning.

Try it like this...

=SUMPRODUCT(--(INDIRECT("'"&P$3&"'!A1:A500")=$C7),--(INDIRECT("'"&P$3&"'!L1:L500")=P$4),INDIRECT("'"&P$3&"'!F1:F500"))

Also, notice that I removed the $ signs from some of the ranges.

Since these ranges are being defined within the INDIRECT function they are TEXT strings and as such they will never change if the formula is copied to other locations and they will automatically be evaluated as absolute references.

Sometimes all those stupid quotes are hard to see/read. Here's the syntax verbatim:

double quote single quote double quote & P$3 & double quote single quote exclamation mark

" ' " & P$3 & " ' !

 

[shemayisroel]

Sometimes all those stupid quotes are hard to see/read. Here's the syntax verbatim:

double quote single quote double quote & P$3 & double quote single quote exclamation mark

" ' " & P$3 & " ' !

Agree - and thank you for taking the time to

a) Resolve it for me - it now works

b) Explaining it a little further

I usually avoid using INDIRECT in this way, however this is what the client wanted

 

[Aladin Akyurek]

Just tried it in the WB and retruned #REF! error.

Here it is

=SUMPRODUCT(--(INDIRECT(""&P$3&"'!$A$1:$A$:500")=$C7),--(INDIRECT(""&P$3&"'!$L$1:$L$500")=P$4),INDIRECT(""&P$3&"'!$F$1:$F$500"))

P3 = sheetname = contains a number than work like "3sample"

3sample sheet does exist and is in lower case

Any ideas...

Should be like:

=SUMPRODUCT(
--(INDIRECT("'"&P$3&"'!A1:A500")=$C7),
--(INDIRECT("'"&P$3&"'!L1:L500")=P$4),
INDIRECT("'"&P$3&"'!F1:F500"))

 

[T. Valko]

Agree - and thank you for taking the time to

a) Resolve it for me - it now works

b) Explaining it a little further

I usually avoid using INDIRECT in this way, however this is what the client wanted

You're welcome. Thanks for the feedback!