• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to use sumproduct function using VBA

hi team

I am making a data where i need to use sumproduct function using VBA. which I am unable to get it done as I am getting error.

Please help.

Thanks
RB
 

Attachments

  • Sumprod.xlsm
    14.4 KB · Views: 32
Hi,

As far i know ,You can't use the SUMPRODUCT in that way!!

Check this..

Code:
Sub abc()
MsgBox Evaluate("=SUMPRODUCT(($A$1:$A$5=""Chetan"")*($B$1:$B$5))")
End Sub

other Alternative.

Code:
MsgBox Application.SumIf(Range("a1:a5"), "Chetan", Range("b1:b5"))
 
To use SUMPRODUCT like that, VB would be:
Code:
MsgBox Evaluate("=SUMPRODUCT((a1:a5 = ""Chetan"") * (b1:b5))")

Edit: Ah, Deepak beat me to it!
 
Try using this
Code:
Sub abc()

Dim myval As String

Range("D1").Formula = "=SUMPRODUCT(($A$1:$A$5=""Chetan"")*($B$1:$B$5))"

myval = Range("D1").Value

Range("D1").ClearContents

MsgBox myval, vbInformation, ""

End Sub
 
Last edited:
@Luke M
@Deepak

I also studied your code & modified it. Didn't know I could calculate formulas in vba itself. So here's my version with inputbox. With dynamic range.

Code:
Sub abc()

Dim MyVal, MyFormula As String

MyVal = Application.InputBox("Please enter the name you want to calculate", "", "Chetan etc.")

If MyVal = False Then

    MsgBox "Macro Cancelled. Macro will now exit", vbCritical, ""
    Exit Sub

ElseIf Len(MyVal) = 0 Or MyVal = "Chetan etc." Or MyVal = "" Then

    MsgBox "Blank value entered. Macro will now exit", vbCritical, ""
    Exit Sub

Else

    MyFormula = "=SUMPRODUCT((A1:A" & Cells(Rows.Count, "A").End(xlUp).Row & "=""" & MyVal & """)*(B1:B" & Cells(Rows.Count, "B").End(xlUp).Row & "))"

    MsgBox Evaluate(MyFormula), vbInformation, ""

End If
End Sub
 
Last edited:
So here's my version with inputbox. With dynamic range.

This one is the short version with dynamic range.

Code:
Sub abc_2()
Dim MyVal As Variant

MyVal = Application.InputBox("Please enter the name you want to calculate", "", "Chetan etc.")

If MyVal = False Or MyVal = "Chetan etc." Or Not Len(MyVal) > 0 Then _
    MsgBox "Macro Cancelled. Macro will now exit", vbCritical, "": Exit Sub

    MsgBox Application.SumIf(Columns(1), "Chetan", Columns(2)), vbInformation, ""

End Sub
 
Good example but yours only gives the Cancel message. Mine gives Cancel & Blank Value entered message. I did do the SUMIF formula but never published it cos of thread title :p
 
Back
Top