1. ## Sumproduct variable (2000)

In my speadsheet, this formula currently works

<font color=blue>=SUMPRODUCT(( <font color=red>'Line 2'</font color=red> !\$N\$3:\$N\$193=\$D\$6)*( <font color=red>'Line 2'</font color=red> !\$B\$3:\$B\$193))</font color=blue>

How can i make the 'Line 2' part , point to a cell instead (so its a variable) Eg:

Cell A1 = Line 2

=SUMPRODUCT(( <font color=magenta>A1</font color=magenta> !\$N\$3:\$N\$193=\$D\$6)*( <font color=magenta>A1</font color=magenta> !\$B\$3:\$B\$193))

<img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

2. ## Re: Sumproduct variable (2000)

Steve, try<pre>=SUMPRODUCT((INDIRECT("'"&A1&"'!\$N\$3:\$N\$19 3")=\$D\$6)*(INDIRECT("'"A1&"'!\$B\$3:\$B\$193"))</pre>

Note the use of single and double quotes!

3. ## Re: Sumproduct variable (2000)

No joy Hans, i *think* there is an & missing as shown, but even so still generates an error. playing with it now though..

<font color=blue>=SUMPRODUCT((INDIRECT("'"&A1&"'!\$N\$3:\$N \$193")=\$D\$6)*(INDIRECT("'" <font color=red>&</font color=red> A1&"'!\$B\$3:\$B\$193"))</font color=blue>

4. ## Re: Sumproduct variable (2000)

Sorry, the formula wasn't correct (an ampersand missing). Try this instead:<pre>=SUMPRODUCT((INDIRECT("'"&A1&"'!\$N\$3: \$N\$193")=\$D\$6)*INDIRECT("'"&A1&"'!\$B\$3:\$B\$193"))</pre>

This one looks the same as yours (I think) but it works in the demo I created.

5. ## Re: Sumproduct variable (2000)

You have a open parentheses before the second indirect which does not have a close parentheses:

Steve

6. ## Re: Sumproduct variable (2000)

Correcting the final bracket gave this formula which works.

<font color=blue>=SUMPRODUCT((INDIRECT("'"&A10&"'!\$N\$3:\$ N\$193")=\$D\$6)*(INDIRECT("'"&A10&"'!\$B\$3:\$B\$193"))) </font color=blue>

yet another Grolsh i owe you Hans! (your total is displayed below, excluding minor accounting errors and Euro-Tax)

7. ## Re: Sumproduct variable (2000)

<img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

The difference between your and my formulas is that I didn't put an extra pair of brackets around the second INDIRECT - both will work.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•