Sum if in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dim eRowP As Long
eRowP = Sheets("Production Log").Cells(Rows.Count, 1).End(xlUp).Row

Range("H11").Formula = "=SUMPRODUCT(--('Production Log'!$H$5:$H$" & eRowP &
"=$C11),--('Production Log'!$K$5:$K$" & eRowP & _
"=H$8),--('Production Log'!$J$5:$J$" & eRowP & "=H$9,1))"

is giving a 1004 Runtime Error

your help is highly appriciated
 
and if rewrite the formula as below, it gives an #Value Error in the Excel
and lemme tell you, all the columns contain text.


Range("H11").Formula = "=SUMPRODUCT(--('Production Log'!$H$5:$H$" & eRowP &
"=$C11),--('Production Log'!$K$5:$K$" & eRowP & _
"=H$8),--('Production Log'!$J$5:$J$" & eRowP & "=H$9,1))"
 
At the end of your formula, you have:
"=H$9,1))"

What's that ,1 (comma one) stuff doing there.

I found it by changing your code to:
Range("H11").Formula = "SUMPRODUCT(.....
(dropped the leading equal sign)

Then I went back to excel to add the equal sign manually. (Sometimes, it's
easier to see the problem in the cell--instead of in code.)
 
Back
Top