SUMPRODUCT macro

[Kim]

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.

 

[Luke M]

'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"

 

[Jacob Skaria]

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" & lngRow).Formula = strFormula

End Sub

If this post helps click Yes