# Changing variables in SumProduct formula

• 01-28-2014, 08:29 AM
Billy C
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 & ")"
• 01-28-2014, 11:57 AM
lecxe
Hi Billy

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

Code:

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

• 01-28-2014, 12:26 PM
Billy C
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
• 01-29-2014, 02:10 AM
Bob Phillips
Do it in a single statement as lecxe shows but don't make it absolute

Code:

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

• 01-29-2014, 02:34 AM
Billy C
That works fantastic, thank you very much indeed.Not much difference in the code but a small tweak makes such a difference
• 01-29-2014, 02:41 AM
lecxe
Hi Billy

Check xld answer, I may have misunderstood what you need and his answer may be what you're looking for.

Quote:

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:

Code:

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:

Code:

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.
• 01-29-2014, 02:43 AM
lecxe
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.

• 01-29-2014, 02:54 AM
Billy C
Thank youYour help has been much appreciated
• 01-29-2014, 03:12 AM
Bob Phillips
Glad it is solved, but a couple oif questions.

1) Should I have made last row absolute

Code:

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?

Code:

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

• 01-29-2014, 03:33 AM
Billy C
Thank you