# Thread: Changing variables in SumProduct formula

1. ## Changing variables in SumProduct formula

I have the following line of code that works for a critera set in sheet Target cell A" and returns in sheet Target cell B2 the correct answer, however when it moves down a row it still refers to A2 not A3can anyone advise me how to progress the A2 to become A3 and so onI have tried storing an intiger in a variable R and changing the code to =Target!A" & R, but i get an errorCells(R, 2).Formula = "=SUMPRODUCT((Data!A2:A" & DataLastRow & " =Target!A2)*Data!B2:B" & DataLastRow & ")"  Reply With Quote

2. Hi Billy

Not sure I understood correctly, but I guess you want something like:

```Worksheets("Target").Range("B2:B5").Formula = _
"=SUMPRODUCT((Data!\$A\$2:\$A\$" & DataLastRow & " =Target!A2)*Data!\$B\$2:\$B\$" & DataLastRow & ")"```  Reply With Quote

3. Hi lecxe
the code is part of a larger piece, what I am trying to do is as I step through moving down a cell after doing some checks it will perform a sum product calculation if I am in cell B5 I want the =Target!A2 to become =Target!A5

Is this something you can help with please?
it all works except this section  Reply With Quote

4. Do it in a single statement as lecxe shows but don't make it absolute

```Worksheets("Target").Range("B2:B5").Formula = _
"=SUMPRODUCT((Data!\$A2:\$A" & DataLastRow & " =Target!A2)*Data!\$B2:\$B" & DataLastRow & ")"```  Reply With Quote

5. That works fantastic, thank you very much indeed.Not much difference in the code but a small tweak makes such a difference  Reply With Quote

6. Hi Billy

Check xld answer, I may have misunderstood what you need and his answer may be what you're looking for. Originally Posted by Billy C ... what I am trying to do is as I step through moving down a cell after doing some checks it will perform a sum product calculation if I am in cell B5 I want the =Target!A2 to become =Target!A5
The way I interpret is is that you want the SumProduct() formula to be written in a cell in Target!B:B and the formula should refer to the cell in the same row in Target!A:A.

The references in worksheet Data, columns A and B are some kind of lookup list and will not change.

1 - Writing the formulas to a group of cells

If this is true, then the statement I posted will write the formula in a group of cells in Target!B:B, each formula referring to the cell to the left in the same row.

Open a new workbook, rename 2 of the sheets to "Data" and "Target" and execute:

```Sub test()
Dim DataLastRow As Long

DataLastRow = 10

Worksheets("Target").Range("B2:B5").Formula = _
"=SUMPRODUCT((Data!\$A\$2:\$A\$" & DataLastRow & " =Target!A2)*Data!\$B\$2:\$B\$" & DataLastRow & ")"

End Sub```
Check the formulas written in Target!B2:B5

2 - Writing 1 formula to 1 cell

If you prefer to write just 1 formula at a time, then an easy way is to use the R1C1 notation and to refer to "RC[-1]" which means a cell in the same row but 1 column to the left. This means that if you write the formula for ex. in Target!B8 then the formula refers to Target!A8

Try:

```Sub test()
Dim DataLastRow As Long

DataLastRow = 10

Worksheets("Target").Range("B8").FormulaR1C1 = _
"=SUMPRODUCT((Data!R2C1:R" & DataLastRow & "C1 =Target!RC[-1])*Data!R2C2:R" & DataLastRow & "C2)"

End Sub```
I hope that you now have enough information to solve your problem.
If not, post back.  Reply With Quote

7. Sorry, can't find the command to delete my previous post.

I see that xld's solution solved your problem so disregard my last post.

I'm glad your problem is solved. Cheers!  Reply With Quote

8. Thank youYour help has been much appreciated  Reply With Quote

9. Glad it is solved, but a couple oif questions.

1) Should I have made last row absolute

```Worksheets("Target").Range("B2:B5").Formula = _
"=SUMPRODUCT((Data!\$A2:\$A\$" & DataLastRow & " =Target!A2)*Data!\$B2:\$B\$" & DataLastRow & ")"```
2) Is SUMPRODUCT necessary here, couldn't you have used SUMIF?

```Worksheets("Target").Range("B2:B5").Formula = _
"=SUMIF(Data!\$A2:\$A" & DataLastRow & " ,Target!A2,Data!\$B2:\$B" & DataLastRow & ")"```  Reply With Quote

10. Thank you  Reply With Quote

#### Posting Permissions

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