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 D230 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("D260")))

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)

 

[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]

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

 

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