Conditional Sumproduct

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 30 product lines across the top of my SS. Each product has 6 columns (Produced, Spoils, Sales etc.). Each product falls into one of two catagories (designated F and E), which is in row 1, in the first column of the 6 for that product (e.g. H1, N1 etc.). Row 2 holds the product price (in the 5th column of the product - eg. L2, R2 etc.)

The second column holds the spoils - dates run down the left so I have spoils in I5,I6,I7 etc and O5,O6,O7 etc.)

What I want to do is calculate the price of the spoils for the F type products and E type products separately, without having to individually select the appropriate cells. Also, the products may change, or new ones added (currently have 20 with space for 10 more).

Is there a way to do this with just built in functions? I have already written a UDF for another calculation in this workbook, so I can do that here, but I would rather use the built in functions in I can.
 
Ok, I solved my problem with the UDF below. The problem now is that (because of the for loops?) when I copy the formula down for all dates (13 weeks), Excel takes about 20-30 secs to calculate cells. This even happens when I copy the cells one at a time. Can someone suggest a way to make this code run more efficiently?

Function WholesaleSpoil(StoreNumber As String, rownum As Integer, Product As String)
Application.Volatile

'make array of wholesale prices
Dim i As Integer, j As Integer
Dim sngPriceArray(30) As Single
j = 1
For i = 8 To 182 Step 6
If Worksheets(StoreNumber).Cells(1, i).Value = Product Then
sngPriceArray(j) = Worksheets(StoreNumber).Cells(2, i + 4)
End If
j = j + 1
Next

'make array of spoil amounts
Dim k As Integer, l As Integer
Dim intSpoilsArray(30) As Integer
l = 1
For k = 8 To 182 Step 6
If Worksheets(StoreNumber).Cells(1, k).Value = Product Then
intSpoilsArray(l) = Worksheets(StoreNumber).Cells(rownum, k + 1)
End If
l = l + 1
Next
'sumproduct the arrays
Dim answer As Single, m As Integer
answer = 0
For m = 1 To 30 Step 1
answer = answer + sngPriceArray(m) * intSpoilsArray(m)
Next

WholesaleSpoil = answer
End Function
 
Marcotte

This UDF will do the calculation almost instantaneously (2.4 Ghz).
(Of course depending on the total number of formulae in your workbook.)
All data is collected in arrays prior to calculation instead of working
directly on the cells. This is a *very* fast of working with data in cells.

I made a test with 41 products over 91 days, with UDF-calls in
F5:G95.

Function WholeSaleSpoil(StoreNumber As String, _
RowNum As Long, Product As String) As Double
'Leo Heuser, 18/19 June 2004
Application.Volatile
Dim Counter As Long
Dim ProdPriceRange As Range
Dim ProdPriceRangeValue As Variant
Dim SpoilRange As Range
Dim SpoilRangeValue As Variant

Product = UCase(Product)

'Last entry is in IN1. The range must be expanded (Step-1)=(6-1)= 5 cells
'from IN2 to IS2 to cover the offset value of "Price". Actually only 4 cells
'are needed, but just in case.

With Worksheets(StoreNumber)
Set ProdPriceRange = .Range("H1:IS2")
Set SpoilRange = .Range("H" & RowNum & ":IS" & RowNum)
End With

ProdPriceRangeValue = ProdPriceRange.Value
SpoilRangeValue = SpoilRange.Value

For Counter = 1 To UBound(ProdPriceRangeValue, 2) Step 6
If UCase(ProdPriceRangeValue(1, Counter)) = Product Then
WholeSaleSpoil = WholeSaleSpoil + _
ProdPriceRangeValue(2, Counter + 4) * _
SpoilRangeValue(1, Counter + 1)
End If
Next Counter

End Function

In F5: =wholesalespoil("Sheet1",ROW(),"E")
In G5: =wholesalespoil("Sheet1",ROW(),"F")

Copy F5:G5 down to F95:G95

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Marcotte A said:
Ok, I solved my problem with the UDF below. The problem now is that
Click to expand...
(because of the for loops?) when I copy the formula down for all dates (13
weeks), Excel takes about 20-30 secs to calculate cells. This even happens
when I copy the cells one at a time. Can someone suggest a way to make this
code run more efficiently?
Function WholesaleSpoil(StoreNumber As String, rownum As Integer, Product As String)
Application.Volatile

'make array of wholesale prices
Dim i As Integer, j As Integer
Dim sngPriceArray(30) As Single
j = 1
For i = 8 To 182 Step 6
If Worksheets(StoreNumber).Cells(1, i).Value = Product Then
sngPriceArray(j) = Worksheets(StoreNumber).Cells(2, i + 4)
End If
j = j + 1
Next

'make array of spoil amounts
Dim k As Integer, l As Integer
Dim intSpoilsArray(30) As Integer
l = 1
For k = 8 To 182 Step 6
If Worksheets(StoreNumber).Cells(1, k).Value = Product Then
intSpoilsArray(l) = Worksheets(StoreNumber).Cells(rownum, k + 1)
End If
l = l + 1
Next
'sumproduct the arrays
Dim answer As Single, m As Integer
answer = 0
For m = 1 To 30 Step 1
answer = answer + sngPriceArray(m) * intSpoilsArray(m)
Next

WholesaleSpoil = answer
End Function
Click to expand...
columns (Produced, Spoils, Sales etc.). Each product falls into one of two
catagories (designated F and E), which is in row 1, in the first column of
the 6 for that product (e.g. H1, N1 etc.). Row 2 holds the product price
(in the 5th column of the product - eg. L2, R2 etc.)products and E type products separately, without having to individually
select the appropriate cells. Also, the products may change, or new ones
added (currently have 20 with space for 10 more).written a UDF for another calculation in this workbook, so I can do that
here, but I would rather use the built in functions in I can.
 
Back
Top