Combine SUMPRODUCT with INDIRECT function

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
 
J

Jim Thomlinson

Your formula is missing the exclamation mark...

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

Arlen

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Named ranges are causing a #REF error 3
INDIRECT function 2
SUMPRODUCT with INDIRECT 4
sums of range within sumproduct 4
Sumproduct with multiple criteria using indirect formula 4
Cacatenation and SUMPRODUCT 4
Sumproduct - several sheets ... indirect? 10
Problem with using INDIRECT with SUMPRODUCT and ROW() 11

Top