Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sumproduct variable (2000)

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

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •