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?
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.
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...
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.