Combine SUMPRODUCT with INDIRECT function

  • Thread starter Thread starter Arlen
  • Start date Start date
A

Arlen

I am trying to use the array power of SUMPRODUCT with the INDIRECT function's
ability to switch sheet names on the fly.

The static formula looks like this:
=SUMPRODUCT(--(Tacoma!B4:B4000=A4))

It works for Tacoma. However, in order to switch sheets (from Tacoma to
Henderson, let's say, I'm trying to incorporate INDIRECT in place of Tacoma,
like so:

=SUMPRODUCT(--(INDIRECT("'"&SideBySide!$B$2&"'B3:B4000")=A4))

I get a #REF error and I'm not sure why. Can INDIRECT be used in this manner?

Any ideas, anyone? I appreciate it.

Thank you for your time.

Arlen
 
Your formula is missing the exclamation mark...

=SUMPRODUCT(--(INDIRECT("'"&SideBySide!$B$2&"'!B3:B4000")=A4))
 
That was it, Jim.

Thanks a bundle!

Arlen

Jim Thomlinson said:
Your formula is missing the exclamation mark...

=SUMPRODUCT(--(INDIRECT("'"&SideBySide!$B$2&"'!B3:B4000")=A4))
Click to expand...
 
Back
Top