PDA

View Full Version : sumproduct in vba



kevvukeka
06-05-2013, 06:34 AM
Hi All,



Can someone help what is the error in the below sumproduct formula. it
returns value O, whereas it should 24500.


I used cel2 to store each value in a range and use that cel2 value in the
sumproduct formula.




Sub test1()
Dim rng1 As Variant
Dim cel2 As Range
Range("AB2",
Range("AB2").End(xlDown)).Select
Selection.Copy
Range("BL2")
Range("BL:BL").RemoveDuplicates Columns:=1,
Header:=xlNo
Set rng1 = Range("BL2", Range("BL2").End(xlDown))
For Each cel2 In rng1
Sheets("Summary").Select
Range("c6") = Evaluate("=SUMPRODUCT((Sheet!AB2:AB32760=" & cel2 & ")*(Sheet!AZ2:AZ32760))")
Next cel2
End Sub



Range AB contains the a list of text values which I copied in BL column and removed the duplicates.

Range AZ contains the Billed amount. I need the sum of AZ range when AB range meets Cel 2 value. But it returns me #Name?.

Can anyone help plz?

SamT
06-05-2013, 08:08 AM
Code Reformatted and unused lines removed
Sub test1()
Dim rng1 As Variant
Dim cel2 As Range

Range("BL:BL").RemoveDuplicates Columns:=1, Header:=xlNo
Set rng1 = Range("BL2", Range("BL2").End(xlDown))
For Each cel2 In rng1
Range("c6") = Evaluate("=SUMPRODUCT((AB2:AB32760=" & cel2 & ")*(AZ2:AZ32760))")
Next cel2

End Sub

kevvukeka
06-06-2013, 01:22 AM
Hi Sam,

The result in Range("C6") still shows #Name?..

Aussiebear
06-06-2013, 02:36 PM
What happens if the cell ranges are wrapped in " "?

kevvukeka
06-10-2013, 04:08 AM
I am still not able to use sum product in vba code. can someone help.

I have made the below code for a user form to apply sum product based on a user input(txtMemberID.value)



Private Sub cmdOK_Click()
Dim rng1 As Range, Dim rng2 As Range, Prng1 As Range, Prng2 As Range,
MemID As Range, crng1 As Range, dedrng1 As Range
Dim policy1 As String, Dim i, j As Integer
i = Sheets("Sheet").Cells(Rows.Count, "E").End(xlUp).Row
j = Sheets("Policy_Details").Cells(Rows.Count, "D").End(xlUp).Row
Set rng1 = Sheets("Sheet").UsedRange
Set rng2 = Sheets("Sheet").Range("E1:E" & i)
Set MemID = Sheets("Sheet").Range("E2:E" & i)
Set crng1 = Sheets("Sheet").Range("Q2:Q" & i)
Set dedrng1 = Sheets("Sheet").Range("AT2:AT" & i)
Set Prng1 = Sheets("Policy_Details").UsedRange
Set Prng2 = Sheets("Policy_Details").Range("D1:D" & j)
policy1 = Application.WorksheetFunction.Index(rng1, _
Application.WorksheetFunction.Match(txtMemberID.Value, rng2, 0), 60)
UserForm1.txtInBrazil.Value = Application.WorksheetFunction.Index(Prng1, _
Application.WorksheetFunction.Match(policy1, Prng2, 0), 51)
UserForm1.txtOutBrazil.Value = Application.WorksheetFunction.Index(Prng1, _
Application.WorksheetFunction.Match(policy1, Prng2, 0), 52)
UserForm1.txtCombined.Value = Application.WorksheetFunction.Index(Prng1, _
Application.WorksheetFunction.Match(policy1, Prng2, 0), 50)
'UserForm1.txtInBrazil.Value = Application.WorksheetFunction.Index(Prng1, _
Application.WorksheetFunction.Match(policy1, Prng2, 0), 50)
UserForm1.txtMInBrazil.Value = Evaluate("=SUMPRODUCT((Sheet!E2:E" & i _
& "=" & UserForm1.txtMemberID.Value & ")*( sheet!BO2:BO" & i _
& "=""In-Brazil"")*(sheet!AT2:AT" & i & "))")
UserForm1.txtMOutBrazil.Value = Evaluate("=SUMPRODUCT((Sheet!E2:E" _
& i & "=" & UserForm1.txtMemberID.Value & ")*( sheet!BO2:BO" _
& i & "=""Out of Brazil"")*(sheet!AT2:AT" & i & "))")


In the last two lines I need to sumproduct one for "Brazil" and one for "not equal to Brazil". I checked google but couldn't get any help.

shrivallabha
06-10-2013, 10:11 AM
Your first post formula doesn't seem to be correct.

Range("c6") = Evaluate("=SUMPRODUCT((Sheet???!AB2:AB32760=" & cel2 & ")*(Sheet???!AZ2:AZ32760))")

If you will upload a workbook then it will be better to see what's happening. A small representative sample should do fine.

Teeroy
06-11-2013, 05:32 AM
You keep overwriting Range("C6") in the For-Next Loop. Unless you have some event driven code that you haven't disclosed you may as well only perform the calculation once, on the last cell of the Range.

snb
06-11-2013, 06:22 AM
Sub M_snb()
Sheets("sheet1").columns(64).RemoveDuplicates Columns:=1, Header:=xlNo


For Each cl In sheets("sheet1").columns(64).specialcells(2).offset(1).specialcells(2)
cl.offset(,-61)= "=SUMPRODUCT((sheet1!AB$2:AB$32760=sheet1!" & cl.address & ")*(sheet1!AZ$2:AZ$32760))"
Next
End Sub



NB 'cel1' is also the name of a cell in the workbook.

kevvukeka
06-11-2013, 09:13 PM
Thanks snb, Thanks Teeroy and Thanks ShreeVallabha...

I could see the error which I was doing with your help......

Thanks...