SumProduct in "for each" loop

[baha]

Hi Everyone,
I have a data page in which column AD is the Id# Of the product,
column AC is the location code, and column R is the cost.Without
creating a filter I want to know total sales, for each product in
different location. I type a code seems like not working. Any jhelp?
Here is my code:

Code:
Sub SumByArea()
Dim cel As Range
Dim totbet, ibet As Variant
For Each cel In Worksheets("Sheet1").Range("AD2:AD50000")
If cel <> "" And IsNumeric(cel) Then
cel.Offset(0, 30) = Application.WorksheetFunction.SumProduct(--
(Sheets("Sheet1").Range("A2:A50000") = cel.Value), --
(Sheets("Sheet1").Range("AC2:AC50000") > 750),
(Sheets("Sheet1").Range("S2:S50000")))
End If
Next cel
End Sub

 

[Don Guillett]

Do you really need to check 50,000 cells?
send me file to test this way dguillett1 @gmail.com or try using

..formula="=sumproduct etc"
or have a look in vba help or google excel:EVALUATE

 

[joeu2004]

I type a code seems like not working. Any jhelp?
Here is my code: [....]
cel.Offset(0, 30) = Application.WorksheetFunction.SumProduct(--
(Sheets("Sheet1").Range("A2:A50000") = cel.Value), --
(Sheets("Sheet1").Range("AC2:AC50000") > 750),
(Sheets("Sheet1").Range("S2:S50000")))

cel.Offset(0, 30) = Evaluate("SUMPRODUCT(--(Sheet1!A2:A50000=" & _
cel.Value & "),--(Sheet1!AC2:AC50000>750), Sheet1!S2:S50000)")

Caveat: This will be very slow over a total of 50000 cells.

 

[joeu2004]

I type a code seems like not working. Any jhelp?
Here is my code: [....]
cel.Offset(0, 30) = Application.WorksheetFunction.SumProduct(--
(Sheets("Sheet1").Range("A2:A50000") = cel.Value), --
(Sheets("Sheet1").Range("AC2:AC50000") > 750),
(Sheets("Sheet1").Range("S2:S50000")))

cel.Offset(0, 30) = Evaluate("SUMPRODUCT(--(Sheet1!A2:A50000=" & _
cel.Value & "),--(Sheet1!AC2:AC50000>750), Sheet1!S2:S50000)")

Caveat: This will be very slow over a total of 50000 cells.

The original algorithm performs about 7.5 billion cell references, which
probably dominates the additional 17.5 billion arithmetic operations.

With just 1000 rows, the original algorithm takes about 1.6 sec on my
computer; YMMV. That is only about 3 million cell references and 7 million
additional arithmetic operations.

The following performs the same operation in less than half the time (for
1000 rows) on my computer.

Sub SumByArea()
Dim f As String
f = "=if(and(d2<>"""",isnumber(d2))," & _
"SUMPRODUCT(--($A$2:$A$50000=d2)," & _
"--($b$2:$b$50000>750),$c$2:$c$50000),"""")"
With Sheets("sheet1").Range("d2:d50000").Offset(0, 30)
.Formula = f
.Value = .Value
End With
End Sub

Nonetheless, I suspect that the execution time is still prohibitive for
50,000 rows.