Re: Re: Indirect function in sumproduct

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: hgrove (hgrove.1adeom_at_excelforum-nospam.com)
Date: 08/02/04 

Date: Mon, 2 Aug 2004 11:54:24 -0500

PeterW wrote...
>Per Norman's reply as below :
>
>|=SUMPRODUCT(('PR Tracking'!$A$7:$A$2500="567W","668N"})
>|*('PR Tracking'!$J$7:$J$2500="capital")
>|*('PR Tracking'!M$7:M$2500))
>|
>|The array parentheses are use to construct the OR that you
>|require.
>
>Question:
>Is it possible to retrieve values from cells. For example, instead
>of {"567W","668N"} , is it possible to write something like
>{D3,D7}?

No. Array *constants* may be written explicitly using braces ("array
parentheses"?), so in {1;2;3;4} or {"A";"B"} all the entries within the
braces are constants. There's no such thing as an array of expressions.
There do seem to be arrays or range objects, but the formula syntax
doesn't support them - they can only be derived from expressions.

If you don't mind hard-coding the ranges references and they should
always be text, try

=SUMPRODUCT(('PR Tracking'!$A$7:$A$2500=T(INDIRECT({"D3","D7"})))
*('PR Tracking'!$J$7:$J$2500="capital")*('PR Tracking'!M$7:M$2500)) 

---
Message posted from http://www.ExcelForum.com/
Quantcast