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

--
Rick (MVP - Excel)


"Jay" wrote in message
...
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.