VBA Sumproduct

[irresistible]

I have 1 to 10 numbers in Col A, when i used Sumif from VBA it worked very well for me.. code below



Private Sub CommandButton1_Click()

Set newwbk = Workbooks.Add
With newwbk
.Sheets(1).Range("A1").Value = WorksheetFunction.SumIf(Range("A1:A10"), "<=5")
End With
End Sub


But when i tried sumproduct (in boolean form) its giving me error type mismatch and blah blah... following is the code i used with exactly the same data:


Private Sub CommandButton1_Click()
Set newwbk = Workbooks.Add
With newwbk
.Sheets(1).Range("A1").Value = WorksheetFunction.SumProduct(--("A1:A10>=3"), --("A1:A10<=6"), "A1:A10")
End With
End Sub

any suggestions? :help

[makako]

have you tried with EVALUATE?
I usually dont like it but try EVALUATE("SSUMPRODUCT(--(A1:A10>=3), --(A1:A10<=6),A1:A10)")

[irresistible]

WOw... that worked for me... however i'll have to check it on my master sheet cuz scenarions are different

[irresistible]

ahh... there is a prob buddy...Just what if one of those arguments contains a Variable...like:

Private Sub CommandButton1_Click()
Dim myinteger as Integer
myinteger = 6
Set newwbk = Workbooks.Add
With newwbk
.Sheets(1).Range("A1").Value = EVALUATE("SUMPRODUCT(--(A1:A10>=3), --(A1:A10<=myinteger),A1:A10)")
End With
End Sub

Its giving Name error on the desired cell... any way out?

[Bob Phillips]

Private Sub CommandButton1_Click()

Set newwbk = Workbooks.Add
With newwbk
.Sheets(1).Range("A1").Value = Application.SumIf(Range("A1:A10"), ">=3") - _
Application.SumIf(Range("A1:A10"), ">6")
End With
End Sub

[Bob Phillips]

ahh... there is a prob buddy...Just what if one of those arguments contains a Variable...like:

Private Sub CommandButton1_Click()
Dim myinteger as Integer
myinteger = 6
Set newwbk = Workbooks.Add
With newwbk
.Sheets(1).Range("A1").Value = EVALUATE("SUMPRODUCT(--(A1:A10>=3), --(A1:A10<=myinteger),A1:A10)")
End With
End Sub
Its giving Name error on the desired cell... any way out?


Private Sub CommandButton1_Click()

Dim myInteger As Integer
myInteger = 6
Set newwbk = Workbooks.Add
With newwbk
.Sheets(1).Range("A1").Value = Application.SumIf(Range("A1:A10"), ">=3") - _
Application.SumIf(Range("A1:A10"), ">" & myInteger)
End With
End Sub

[makako]

Private Sub CommandButton1_Click()
Dim myinteger As Integer
myinteger = 6
Set newwbk = Workbooks.Add
With newwbk
.Sheets(1).Range("A1").Value = EVALUATE("SUMPRODUCT(--(A1:A10>=3), --(A1:A10<=" & myinteger & "),A1:A10)")
End With
End Sub

[irresistible]

Thanks buddies... You people really saved my tons of hours... cuz i never learnt about Evaluate thingy before...
XLD-->I'll be using MAKAKO's code cuz that one will serve better specifically for my case... anyways cheer up buddy and I appreciate your response...

[Bob Phillips]

Evaluate is slow and inefficient, and should be avoided if possible.