Flipping a sumproduct

[ajmckenna]

I have a sumproduct calculation problem.

is there a way to enter the second range backwards

ex. first number is in A1

Row 1= 10 20 30 40
Row 2 1 2 3 4

instead of sumproduct(A1:a4,B1:b4) result=300

I want a result of 200 or 10*4 +20*3 +30*2+40*1 without having to flip the order of row2

thanks

 

[ajmckenna]

whoops

I meant sumproduct(A1:D1,A2:D2)
thanks

 

[Aladin Akyurek]

=SUMPRODUCT(A1:D1,N(OFFSET(A2,0,COLUMN(A2)+COLUMNS(A2:D2)-COLUMN(A2:D2)-1,1)))


Addendum. If there are no empty cells in the range to reverse, use...

=SUMPRODUCT(A1:D1,N(OFFSET(A2,0,COLUMN(A2)+COUNT(A2:D2)-COLUMN(A2:D2)-1,1)))

which has one less volatile function.
This message was edited by Aladin Akyurek on 2002-11-01 17:40

 

[Mark W.]

The formula in cell A6 below has no volatile functions, but uses the Morefunc.xll...

Book1
	A	B	C	D	E	F	G	H	I	J
1	10	20	30	40
2	1	2	3	4
3
4	200
5	200
6	200
7
Sheet2

This message was edited by Mark W. on 2002-11-01 17:54

 

[ajmckenna]

Mark & Aladin
Thanks for you help I will either have the data I want to flip brought into a new range & do traditional sumproduct or use aladin's formula. I will have 12 variable inputs(as opposed to 4 (4,3,2,1) in my example) and it will be a rolling formula, also other people will use file to morefunc is not an option. I think one of these methods is the way to go thanks for all the help.