help on replacing this formula

G

Guest

Hi. I am new to excel macros/vba/worksheet functions although I have used VBA
in Access. I have a user who has a worksheet with this formula:
((($R23*U23)+($R24*U24)+($R25*U25)+($R26*U26)+($R27*U27)+($R28*U28)+($R29*U29)+($R30*U30)+($R31*U31)+($R32*U32)+($R33*U33)+($R34*U34)+($R35*U35)+($R36*U36)+($R37*U37)+($R38*U38)+($R39*U39)+($R40*U40)+($R41*U41)+($R42*U42)+($R43*U43)+($R44*U44)+($R45*U45)+($R46*U46)+($R47*U47)+($R48*U48)+($R50*U50)+($R51*U51)+($R52*U52)+($R53*U53)+($R54*U54)+($R55*U55)+($R56*U56)+($R57*U57)+($R58*U58)+($R59*U59)+($R60*U60)+($R61*U61)+($R62*U62)+($R63*U63)+($R64*U64)+($R65*U65)+($R66*U66)+($R67*U67)+($R68*U68)+($R69*U69)+($R70*U70)+($R71*U71)+($R72*U72)+($R73*U73)+($R74*U74)+($R75*U75)+($R76*U76)+($R77*U77)+($R78*U78)+($R79*U79)+($R80*U80)+($R81*U81)+($R82*U82)+($R83*U83)+($R84*U84)+($R85*U85)+($R86*U86)+($R87*U87)+($R88*U88)+($R89*U89)+($R90*U90)+($R91*U91)+($R92*U92)+($R93*U93)+($R94*U94)+($R95*U95)+($R96*U96)+($R97*U97)+($R98*U98)+($R99*U99)+($R100*U100)+($R101*U101)+($R102*U102)+($R103*U103)+($R104*U104)+($R105*U105)+($R106*U106)+($R107*U107)+($R108*U108)+($R109*U109)+($R110*U110)+($R111*U111)+($R126*U126))*U21)/1000
and really all it is doing is multiplying column r by U and then
accumulating the results. I will also have to do the same thing by
multiplying column R by V and then accumulating the results, etc.
I am guessing this there is a very basic way to do it in excel. I know the
data on the spread sheet does contain some blank rows, if that makes a
difference.
Can anyone help me get started on this?
 
G

Guest

Hi,

You can use a helper column say X and enter the formula below in X1 and the
copy drag down to where ever you need say X100 and sume column X in the cell
X101:

=R1*U1

Thanks,
 
P

Peo Sjoblom

=SUMPRODUCT(R23:R111,U23:U111)

is it true it skips rows from R111 to R126 at the end and that the total is
multiplied by U21 and then divided by 1000?

If so

=((SUMPRODUCT(R23:R111,U23:U111)+(R126*U123))*U21)/1000
 
D

David Biddulph

A small typo there?

=((SUMPRODUCT(R23:R111,U23:U111)+(R126*U126))*U21)/1000 ?
 
Top