Problem coding SumProduct in VBA 2003

[technowerk]

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.

Company	Employee	Emp_grade	Year	Salary
1	A	3	2000	2500
1	B	2	1997	4000
1	A	3	2002	2750
1	C	2	1998	5500
1	D	3	1998	1500
1	A	3	2003	3000
2	A	3	2004	3500
2	E	1	2000	6000
3	A	2	2005	3750
2	A	2	2005	4500
2	F	2	2001	4000
2	D	2	2001	6000
2	G	1	2001	7500
3	A	1	2008	5500
3	B	3	2000	2500
3	F	3	1990	1500
3	H	2	2004	5000
3	I	1	2010	7000
3	J	3	2000	3000
4	F	1	2008	5500
4	K	1	2009	6000
4	A	1	2009	5750
1	A	1	2010	6000
2	A	1	2011	7500

<tbody>
</tbody>

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

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!

 

[JoeMo]

Try:

TOT_SUM = _
    Application.WorksheetFunction.SumProduct((Range("B2:B65535") = _
    "A") * (Range("C2:C65535") = 3), Range("E2:E65535"))

 

[Norie]

Try this.

TOT_SUM = Evaluate("SUMPRODUCT(--(B2:B65535=""A""), --(C2:C65535=3), E2:E65535)")

 

[Oeldere]

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

 

[Biz]

Another variant with last row as variable.

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

 

[technowerk]

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?

 

[Biz]

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?

Evaluate Method converts a Microsoft Excel name to an object or a value

 

[justwitt]

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.

 

[Biz]

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.

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

Biz