A Challenge for Excel Gurus - Sumproduct does NOT work!

Actuariojf

New Member
Joined
Aug 29, 2002
Messages
15
Can anybody help me to find a way to do the following calculations in Excel:

Given:
Column A Column B
A1 = 1 B1 = 10
A2 = 2 B2 = 11
A3 = 3 B3 = 12
A4 = 4 B4 = 14

I would like to get a excel function or procedure that I will input JUST ONCE on C1 and copy down to C2..C4 with the following results:

C1=A1*B1=1*10=10

C2=A1*B2+A2*B1=1*11+2*10=31

C3=A1*B3+A2*B2+A3*B1=1*12+2*11+3*10=64

C4=A1*B4+A2*B3+A3*B2+A4*B1
=1*13+2*12+3*11+4*10
= 110

It seem easy, but it is not. Try to do it and please somebody help me!

Thanks!
This message was edited by Actuariojf on 2002-08-30 10:50
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-08-30 10:36, Actuariojf wrote:
Can anybody help me to find a way to do the following calculations in Excel:

Given:
Column A Column B
A1 = 1 B1 = 10
A2 = 2 B2 = 11
A3 = 3 B3 = 12
A4 = 4 B4 = 14

I would like to get a excel function or procedure that I will input JUST ONCE on C1 and copy down to C2..C4 with the following results:

C1=A1*B1=1*10=10

C2=A1*B2+A2*B1=1*11+2*10=31

C3=A1*B3+A2*B2+A3*B1=1*12+2*11+3*10=64

C4=A1*B4+A2*B3+A3*B2+A4*B1
=1*13+2*12+3*11+4*10
= 110

It seem easy, but it is not. Try to do it and please somebody help me!

Thanks!
This message was edited by Actuariojf on 2002-08-30 10:50

In C1 enter & copy down:

=SUMPRODUCT($A$1:A1,N(OFFSET($B$1,ROW($B$1)+ROWS($B$1:B1)-ROW($B$1:B1)-1,0)))

Note. The credit must go in part also to Juan Pablo. :biggrin:
 

gwkenny

Well-known Member
Joined
Aug 13, 2002
Messages
565
Wow.

I'm jealous. That was pretty nice.

Didn't know you could use ranges like that. I was trying to solve this with array formulas and every time I went control enter errrrrrrrrrr #value :)

Thought it would be simple returning a list in reverse order. Gahh (well it is, after seeing your solution, hehe)

g-
 

XL-Dennis

Well-known Member
Joined
Jul 27, 2002
Messages
1,920
Actuariojf,

Go with Aladins excellenta formula - I misread the problem ;)

Kind regards,
Dennis
This message was edited by XL-Dennis on 2002-08-30 15:03
 

Actuariojf

New Member
Joined
Aug 29, 2002
Messages
15

ADVERTISEMENT

Thanks so much! It works nice and great!
 

Jay Petrulis

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
Here is a UDF which does the job for data set up in columns...<pre>Function SUMPRODUCT_INVERT(Rng1 As Range, Rng2 As Range) As Double
Dim fn As WorksheetFunction
Dim i As Long, Arr1, Arr2
Dim TotalSum As Double

Set fn = Application.WorksheetFunction

If Rng1.Cells.Count<> Rng2.Cells.Count Then
SUMPRODUCT_INVERT = CVErr(xlErrValue)
Exit Function
End If

If Rng1.Cells.Count = 1 Then
SUMPRODUCT_INVERT = Rng1 * Rng2
Exit Function
End If

Arr1 = Rng1.Value
Arr2 = Rng2.Value

For i = LBound(Arr1, 1) To UBound(Arr1, 1)
If IsNumeric(Arr1(i, 1)) And IsNumeric(Arr2(UBound(Arr2, 1) - i + 1, 1)) Then
TotalSum = TotalSum + Arr1(i, 1) * Arr2(UBound(Arr2, 1) - i + 1, 1)
End If
Next i

SUMPRODUCT_INVERT = TotalSum

End Function</pre>

I haven't checked, but the other options might require the data to start in row 1 to work properly. This will handle any starting points, and the others undoubtedly could be adapted to handle those situations, too.

_________________
Bye,
Jay
This message was edited by Jay Petrulis on 2002-08-30 15:12
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
I haven't checked, but the other options might require the data to start in row 1 to work properly. This will handle any starting points, and the others undoubtedly could be adapted to handle those situations, too.

Naughty of you... What I suggested will work properly even if you insert columns at the left side or add/insert rows before the data.

:biggrin:
 

Forum statistics

Threads
1,144,360
Messages
5,723,903
Members
422,526
Latest member
edminty84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top