Using SumProduct in VB

[Michael]

I was hoping someone can help me.

I am writing a script that parses though an excel file and extract the
unique entries and sums the quantities with respect to certain criteria (ie
Month or Year). I currently have an excel formula that does what I am looking
for, but I need to convert it to a VB script.

The formula looks like:

=SUMPRODUCT(($C$2:$C$30="boxes")*($E$2:$E$30="January")*($D$2:$D$30))

Where C2:C30 contains the item, E2:E30 is the month, and D2:D30 contains
the quantity of the items. So this example counts the number of boxes from
January in this data range.


In my script, I am trying to write:

Sheets("Summary").Cells(n, 4) =
Application.WorksheetFunction.SumProduct((Range("C2:C30") = "boxes")
*(Range("E2:E30") = "January")* (Range("D2:D60")))

However, when I run it, I get a "Run-time 13 error : Type mismatch" error. I
have been unable to debug this statement. So I was hoping someone could help
me with this.

I greatly appreciate any assistance you could provide.

Thanks,

-Michael

[Bob Phillips]

Use Evaluate

Sheets("Summary").Cells(n, 4) =
Activesheet.Evaluate("SUMPRODUCT(($C$2:$C$30=""boxes"")*" & _

"($E$2:$E$30=""January"")*($D$2:$D$30))")


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Michael" <[email protected]> wrote in message
news:[email protected]...
> I was hoping someone can help me.
>
> I am writing a script that parses though an excel file and extract the
> unique entries and sums the quantities with respect to certain criteria
(ie
> Month or Year). I currently have an excel formula that does what I am
looking
> for, but I need to convert it to a VB script.
>
> The formula looks like:
>
> =SUMPRODUCT(($C$2:$C$30="boxes")*($E$2:$E$30="January")*($D$2:$D$30))
>
> Where C2:C30 contains the item, E2:E30 is the month, and D2:D30 contains
> the quantity of the items. So this example counts the number of boxes from
> January in this data range.
>
>
> In my script, I am trying to write:
>
> Sheets("Summary").Cells(n, 4) =
> Application.WorksheetFunction.SumProduct((Range("C2:C30") = "boxes")
> *(Range("E2:E30") = "January")* (Range("D2:D60")))
>
> However, when I run it, I get a "Run-time 13 error : Type mismatch" error.
I
> have been unable to debug this statement. So I was hoping someone could
help
> me with this.
>
> I greatly appreciate any assistance you could provide.
>
> Thanks,
>
> -Michael

[Bernie Deitrick]

Michael,

Use, for example:

Dim myCount As Integer
myCount = Evaluate("=SUMPRODUCT(($C$2:$C$30=""boxes"")*($E$2:$E$30=""January"")*($D$2:$D$30))")
MsgBox "There were " & myCount & " boxes shipped in January."

HTH,
Bernie
MS Excel MVP


"Michael" <[email protected]> wrote in message
news:[email protected]...
>I was hoping someone can help me.
>
> I am writing a script that parses though an excel file and extract the
> unique entries and sums the quantities with respect to certain criteria (ie
> Month or Year). I currently have an excel formula that does what I am looking
> for, but I need to convert it to a VB script.
>
> The formula looks like:
>
> =SUMPRODUCT(($C$2:$C$30="boxes")*($E$2:$E$30="January")*($D$2:$D$30))
>
> Where C2:C30 contains the item, E2:E30 is the month, and D2:D30 contains
> the quantity of the items. So this example counts the number of boxes from
> January in this data range.
>
>
> In my script, I am trying to write:
>
> Sheets("Summary").Cells(n, 4) =
> Application.WorksheetFunction.SumProduct((Range("C2:C30") = "boxes")
> *(Range("E2:E30") = "January")* (Range("D2:D60")))
>
> However, when I run it, I get a "Run-time 13 error : Type mismatch" error. I
> have been unable to debug this statement. So I was hoping someone could help
> me with this.
>
> I greatly appreciate any assistance you could provide.
>
> Thanks,
>
> -Michael

[Michael]

Bernie,

Thank you. I do have one more question for you. How can I do the same thing,
but be a little more modular:

Like:
Evaluate("=SUMPRODUCT(($C$2:$C$30=Sheets("Inventory").cells(n,1))*($E$2:$E$30=""January"")*($D$2:$D$30))")

I want to use the same function for each item. I was playing around with it,
but i could not get it to return the right value.

Thanks!

-Michael

"Bernie Deitrick" wrote:

> Michael,
>
> Use, for example:
>
> Dim myCount As Integer
> myCount = Evaluate("=SUMPRODUCT(($C$2:$C$30=""boxes"")*($E$2:$E$30=""January"")*($D$2:$D$30))")
> MsgBox "There were " & myCount & " boxes shipped in January."
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Michael" <[email protected]> wrote in message
> news:[email protected]...
> >I was hoping someone can help me.
> >
> > I am writing a script that parses though an excel file and extract the
> > unique entries and sums the quantities with respect to certain criteria (ie
> > Month or Year). I currently have an excel formula that does what I am looking
> > for, but I need to convert it to a VB script.
> >
> > The formula looks like:
> >
> > =SUMPRODUCT(($C$2:$C$30="boxes")*($E$2:$E$30="January")*($D$2:$D$30))
> >
> > Where C2:C30 contains the item, E2:E30 is the month, and D2:D30 contains
> > the quantity of the items. So this example counts the number of boxes from
> > January in this data range.
> >
> >
> > In my script, I am trying to write:
> >
> > Sheets("Summary").Cells(n, 4) =
> > Application.WorksheetFunction.SumProduct((Range("C2:C30") = "boxes")
> > *(Range("E2:E30") = "January")* (Range("D2:D60")))
> >
> > However, when I run it, I get a "Run-time 13 error : Type mismatch" error. I
> > have been unable to debug this statement. So I was hoping someone could help
> > me with this.
> >
> > I greatly appreciate any assistance you could provide.
> >
> > Thanks,
> >
> > -Michael
>
>
>

[Bob Phillips]

Evaluate("=SUMPRODUCT(($C$2:$C$30=""" & Sheets("Inventory").cells(n,1) &
""")*($E$2:$E$30=""January"")*($D$2:$D$30))")

assuming that it is text

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Michael" <[email protected]> wrote in message
news:[email protected]...
> Bernie,
>
> Thank you. I do have one more question for you. How can I do the same
thing,
> but be a little more modular:
>
> Like:
>
Evaluate("=SUMPRODUCT(($C$2:$C$30=Sheets("Inventory").cells(n,1))*($E$2:$E$3
0=""January"")*($D$2:$D$30))")
>
> I want to use the same function for each item. I was playing around with
it,
> but i could not get it to return the right value.
>
> Thanks!
>
> -Michael
>
> "Bernie Deitrick" wrote:
>
> > Michael,
> >
> > Use, for example:
> >
> > Dim myCount As Integer
> > myCount =
Evaluate("=SUMPRODUCT(($C$2:$C$30=""boxes"")*($E$2:$E$30=""January"")*($D$2:
$D$30))")
> > MsgBox "There were " & myCount & " boxes shipped in January."
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Michael" <[email protected]> wrote in message
> > news:[email protected]...
> > >I was hoping someone can help me.
> > >
> > > I am writing a script that parses though an excel file and extract the
> > > unique entries and sums the quantities with respect to certain
criteria (ie
> > > Month or Year). I currently have an excel formula that does what I am
looking
> > > for, but I need to convert it to a VB script.
> > >
> > > The formula looks like:
> > >
> > > =SUMPRODUCT(($C$2:$C$30="boxes")*($E$2:$E$30="January")*($D$2:$D$30))
> > >
> > > Where C2:C30 contains the item, E2:E30 is the month, and D2:D30
contains
> > > the quantity of the items. So this example counts the number of boxes
from
> > > January in this data range.
> > >
> > >
> > > In my script, I am trying to write:
> > >
> > > Sheets("Summary").Cells(n, 4) =
> > > Application.WorksheetFunction.SumProduct((Range("C2:C30") = "boxes")
> > > *(Range("E2:E30") = "January")* (Range("D2:D60")))
> > >
> > > However, when I run it, I get a "Run-time 13 error : Type mismatch"
error. I
> > > have been unable to debug this statement. So I was hoping someone
could help
> > > me with this.
> > >
> > > I greatly appreciate any assistance you could provide.
> > >
> > > Thanks,
> > >
> > > -Michael
> >
> >
> >