SUMPRODUCT macro

  • Thread starter Thread starter Kim
  • Start date Start date
K

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.
 
'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"
 
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
 
Back
Top