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.

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


"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.

Try

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

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

--
Jacob


"Jay" wrote:

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.

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.