SUMPRODUCT macro
 

I'm trying to put the following formula into cell P2. Can someone please help
with the correct macro.

=SUMPRODUCT(($C$2:$C$112=C2)*($F$2:$F$112=F2)*(N2 $N$2:$N$112))+1

I want the row 112 to be flexible. Depending on how many row there are.

Thanks.

SUMPRODUCT macro
 

'Some method for determining amount of rows
'would go here
x = 112

'Combining formula
Range("P2").Formula = "=SUMPRODUCT(($C$2:$C$" & x & "=C2)" & _
"*($F$2:$F$" & x & "2=F2)*(N2$N$2:$N$" & x & "))+1"
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Kim" wrote:

I'm trying to put the following formula into cell P2. Can someone please help
with the correct macro.

=SUMPRODUCT(($C$2:$C$112=C2)*($F$2:$F$112=F2)*(N2 $N$2:$N$112))+1

I want the row 112 to be flexible. Depending on how many row there are.

Thanks.

SUMPRODUCT macro
 

Try the below macro and feedback


Sub Macro()
Dim lngRow As Long
lngRow = Cells(Rows.Count, "C").End(xlUp).Row
strFormula = "=SUMPRODUCT(($C$2:$C$" & lngRow & "=C2)*($F$2:$F$" & _
lngRow & "=F2)*(N2$N$2:$N$" & lngRow & "))+1"

Range("P2").Formula = strFormula

'OR if you want to apply allthrough col P
'Range("P2:P" & lngRow).Formula = strFormula

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Kim" wrote:

I'm trying to put the following formula into cell P2. Can someone please help
with the correct macro.

=SUMPRODUCT(($C$2:$C$112=C2)*($F$2:$F$112=F2)*(N2 $N$2:$N$112))+1

I want the row 112 to be flexible. Depending on how many row there are.

Thanks.