Sum if in VBA

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
--
Thanks,
Baapi

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))"
--
Baapi


"Baapi" wrote:

> 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
> --
> Thanks,
> Baapi

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.)




Baapi wrote:
>
> 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))"
> --
> Baapi
>
> "Baapi" wrote:
>
> > 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
> > --
> > Thanks,
> > Baapi

--

Dave Peterson