Another SumProduct Question

[Jay]

Here's a simple example of something I'm trying to figure out.
A B
1 5
1 7
2 4
2 9

Can I calculate (5*4)+(7*9)=83 using a sumproduct?

I tried this but it doesn work for what I think is an obvious reason.
SumProduct(((B1:B4)*(A1:A4=1)),((B1:B4)*(A1:A4=2))). I believe this isn't
working because there is never a case where A1:A4=1 AND A1:A4=2. Therefore I
get the answer = 0.

I'm also having a hard time trying to figure out how I would do this in a
loop of some kind as an alternative. Can I define B1:B2 as a range and B3:B4
as a range and pass them to a function that computes B1*B3 + B2*B4? How do I
define those ranges in code?

Thanks for your help.

 

[Patrick Molloy]

I did it with a UDF:

Option Explicit

Function sumP(source As Range)

Dim index1 As Long
Dim index2 As Long
Dim data() As Long
Dim result As Long
Dim index As Long

ReDim data(1 To 2, 1 To source.Rows.Count) As Long

For index = 1 To source.Rows.Count
Select Case source.Cells(index, 1)
Case 1
index1 = index1 + 1
data(1, index1) = source.Cells(index, 2)
Case 2
index2 = index2 + 1
data(2, index2) = source.Cells(index, 2)
End Select
Next
For index = 1 To UBound(data, 2)
result = result + data(1, index) * data(2, index)
Next
sumP = result
End Function

I'm pretty sure that there's a better way. pass the range (both columns) to
the function. The loop pairs off the first 1 with the first 2, the second 1
with the second 2 and so on .... at least that's how I understood your
query.
once the values are paired up, the second loop simply multiplies and sums
them

 

[Jacob Skaria]

Try

Sub Macro()
MsgBox WorksheetFunction.SumProduct(Range("B1:B2"), Range("B3:B4"))
End Sub

'formula
=SUMPRODUCT((B1:B2)*(B3:B4))

 

[Rick Rothstein]

Assuming you want to be able to mix the location of the 1's and 2's around,
try this UDF...

Function SpecialMultiplyAdd(R As Range) As Double
Dim X As Long, Counter1 As Long, Counter2 As Long
Dim Parts1(1 To 2) As Double, Parts2(1 To 2) As Double
If R.Rows.Count = 4 And R.Columns.Count = 2 Then
For X = 1 To 7 Step 2
If R(X).Value = 1 Then
Counter1 = Counter1 + 1
Parts1(Counter1) = R(X).Offset(, 1).Value
ElseIf R(X).Value = 2 Then
Counter2 = Counter2 + 1
Parts2(Counter2) = R(X).Offset(, 1).Value
End If
Next
SpecialMultiplyAdd = Parts1(1) * Parts2(1) + Parts1(2) * Parts2(2)
End If
End Function