| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup combine indirect | Excel Worksheet Functions | |||
| Need help with using SUMPRODUCT with INDIRECT | Excel Worksheet Functions | |||
| include INDIRECT function into SUMPRODUCT formula | Excel Worksheet Functions | |||
| Combine Indirect and Sumif | Excel Worksheet Functions | |||
| sumproduct & indirect | Excel Worksheet Functions | |||
Linear Mode
