Combine SUMPRODUCT with INDIRECT function
 

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

Combine SUMPRODUCT with INDIRECT function
 

Your formula is missing the exclamation mark...

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

--
HTH...

Jim Thomlinson


"Arlen" wrote:

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

Combine SUMPRODUCT with INDIRECT function
 

That was it, Jim.

Thanks a bundle!

Arlen

"Jim Thomlinson" wrote:

Your formula is missing the exclamation mark...

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

--
HTH...

Jim Thomlinson


"Arlen" wrote:

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