Problem coding SumProduct in VBA 2003

T

technowerk

New Member
Joined
Oct 30, 2012
Messages
5
Hi,

Following is a sample data I have. I have written a macro that does various calculations for me. However, now I'm stuck on coding SumProduct function.

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Company[/TD]
[TD="class: xl63, width: 64"]Employee[/TD]
[TD="class: xl63, width: 64"]Emp_grade[/TD]
[TD="class: xl63, width: 64"]Year[/TD]
[TD="class: xl63, width: 64"]Salary[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]2000[/TD]
[TD="class: xl63, align: right"]2500[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"]B[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]1997[/TD]
[TD="class: xl63, align: right"]4000[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]2002[/TD]
[TD="class: xl63, align: right"]2750[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"]C[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]1998[/TD]
[TD="class: xl63, align: right"]5500[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"]D[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]1998[/TD]
[TD="class: xl63, align: right"]1500[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]2003[/TD]
[TD="class: xl63, align: right"]3000[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]2004[/TD]
[TD="class: xl63, align: right"]3500[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"]E[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2000[/TD]
[TD="class: xl63, align: right"]6000[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2005[/TD]
[TD="class: xl63, align: right"]3750[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2005[/TD]
[TD="class: xl63, align: right"]4500[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"]F[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2001[/TD]
[TD="class: xl63, align: right"]4000[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"]D[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2001[/TD]
[TD="class: xl63, align: right"]6000[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"]G[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2001[/TD]
[TD="class: xl63, align: right"]7500[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2008[/TD]
[TD="class: xl63, align: right"]5500[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]B[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]2000[/TD]
[TD="class: xl63, align: right"]2500[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]F[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]1990[/TD]
[TD="class: xl63, align: right"]1500[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]H[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2004[/TD]
[TD="class: xl63, align: right"]5000[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]I[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2010[/TD]
[TD="class: xl63, align: right"]7000[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]J[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]2000[/TD]
[TD="class: xl63, align: right"]3000[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63"]F[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2008[/TD]
[TD="class: xl63, align: right"]5500[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63"]K[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2009[/TD]
[TD="class: xl63, align: right"]6000[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2009[/TD]
[TD="class: xl63, align: right"]5750[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2010[/TD]
[TD="class: xl63, align: right"]6000[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2011[/TD]
[TD="class: xl63, align: right"]7500[/TD]
[/TR]
</tbody>[/TABLE]

This data is filtered data (I have applied advanced filter to get this data). Now I would like to sum 'Salary' when Employee=A AND Emp_grade=3.

As SUMIF works ONLY for 1 condition, I had to opt for SumProduct.

Here's what I've coded

Code: 
Dim TOT_SUM As Double

TOT_SUM=Application.WorksheetFunctions.SumProduct((B2:B65535="A"),(C2:C65535=3),E2:E65535)

This gives an error. I tried recording a macro and using the same code in my code but it doesn't work either. Can someone help please?

I do not want to use pivot table as the actual data is too large to fit on a single sheet of pivot table (about 95 columns and over 12000 rows) and thus there's a risk of losing data if I create a pivot table.

Is there any other function that I can use instead of SumProduct to combine 2 or more conditions in the same statement?

Thanks in advance!
 
Last edited:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Try:
Code: 
TOT_SUM = _
    Application.WorksheetFunction.SumProduct((Range("B2:B65535") = _
    "A") * (Range("C2:C65535") = 3), Range("E2:E65535"))
 
Upvote 0
Try this.
Code: 
TOT_SUM = Evaluate("SUMPRODUCT(--(B2:B65535=""A""), --(C2:C65535=3), E2:E65535)")
 
Upvote 0
I do not want to use pivot table as the actual data is too large to fit on a single sheet of pivot table (about 95 columns and over 12000 rows) and thus there's a risk of losing data if I create a pivot table.



If your columns are condensed, you can use pivottable.

Before making a pivot table, you can make an table of your data.

Excel 2007 => Insert => table
 
Upvote 0
Another variant with last row as variable.

Code: 
Sub Test()
    Dim s As String
    Dim LR As Long
    Dim TOT_SUM As Double
    
    LR = Cells(Rows.Count, "A").End(xlUp).Row
    s = "SUMPRODUCT(--($B$2:$B$" & LR & "=" & Chr(34) & "A" & Chr(34) & ")," & "--(" & "C2:C" & LR & "=3" & ")," & "E2:E" & LR & ")"
    Debug.Print s
    TOT_SUM = Application.Evaluate(s)
End Sub

Biz
 
Upvote 0
Thank you all for all the valuable inputs! I tried this today and Norie's method seems to work. Can someone please explain what 'Evaluate' exactly does?
 
Upvote 0
technowerk said:
Thank you all for all the valuable inputs! I tried this today and Norie's method seems to work. Can someone please explain what 'Evaluate' exactly does?
Click to expand...

Evaluate Method converts a Microsoft Excel name to an object or a value
 
Upvote 0
In what I can see it looks like you are missing a parentheses at the end but I suspect it just didn't get copied when you posted this.
You might be able to try separating your conditions with "*" instead of "," that sometimes makes Sumproduct work better in Excel 03 at least. Or you could just write a loop that would do it.

You can use the AND statement in an If to get multiple conditions

sum=0
For i=2 to 65535
If Cells(i,2).Value="A" AND Cells(i,3).Value=3 Then
sum=sum+Cells(i,5)
End If
Next i

You can certainly modify to fit the data instead of going through all 65535 rows.
 
Last edited:
Upvote 0
justwitt said:
In what I can see it looks like you are missing a parentheses at the end but I suspect it just didn't get copied when you posted this.
You might be able to try separating your conditions with "*" instead of "," that sometimes makes Sumproduct work better in Excel 03 at least. Or you could just write a loop that would do it.

You can use the AND statement in an If to get multiple conditions

sum=0
For i=2 to 65535
If Cells(i,2).Value="A" AND Cells(i,3).Value=3 Then
sum=sum+Cells(i,5)
End If
Next i

You can certainly modify to fit the data instead of going through all 65535 rows.
Click to expand...

Why loop its slow? My post #5 gets last row and it's not hard coding last row as 65535.

Biz
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
405
Asbestos_Jen
Asbestos_Jen
K
  • Question Question
Formula for dynamic forecast
Replies
0
Views
266
Keven
K
O
  • Solved
Find and match header in different location for the column based on sheet name
Replies
8
Views
95
Omar M
O
F
  • Solved
Problems filtering a Pivot Table with VBA
Replies
2
Views
850
Domenic
D
O
  • Question Question
sum or subtract for the same cell based on match column between two sheets
2
Replies
13
Views
823
Omran Y
O

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top