Forum Discussion
Which function to use to sum products of specific cells in a row
What function or way to calculate the sum of the *product of one cell by the very next cell and then the following cell by its very next cell &* so on .. all in the same row ?? .. (up to a max specified cell in the same horizontal row)
example =L4*M4+N4*O4+P4*Q4+R4*S4+T4*U4 ... so on .. V4*W4+X4*Y4 .....
As variant
=SUMPRODUCT( INDEX(L4:Y4, COLUMN( A1:INDEX( 1:1, COLUMNS(L4:Y4) ) )*2 ), INDEX(L4:Y4, COLUMN( A1:INDEX( 1:1, COLUMNS(L4:Y4) ) )*2 -1 ) )It doesn't matter what is in A1, that is to generate column numbers in the range. That works like
=SUMPRODUCT( INDEX(L4:U4, COLUMN( A1:INDEX( 1:1, COLUMNS(L4:U4)/2 ) )*2 ), INDEX(L4:U4, COLUMN( A1:INDEX( 1:1, COLUMNS(L4:U4)/2 ) )*2 -1 ) )It was misprint in previous variant, sorry.
- Thank you Sergei Baklan .. can you clarify what A1 represents ..
=SUMPRODUCT(L4:INDIRECT(M1)*ISEVEN(COLUMN(L4:INDIRECT(M1)))*M4:INDIRECT(N1)*ISODD(COLUMN(M4:INDIRECT(N1))))Maybe with this formula. In M1 and N1 you can dynamically specify the maximum cells.
- Thanks Quadruple_Pawn .. it worked well for the first row .. but ..
when I fill the same formula to the cell underneath in the next row, how can I make the specified maximum cells stay where they are (i.e. M1 & T1) but make their values adjust to the next row (i.e. become T5 & U5 and then T6 & U6 and so on ... )
(pls note that I used the formula you gave me in the same row as the where the data is ,, i.e. row 4 and specifically in K4 and now I need to fill the same into K5 & K6 and so on ... )=SUMPRODUCT(L4:INDIRECT($M$1&ROW(4:4))*ISEVEN(COLUMN(L4:INDIRECT($M$1&ROW(4:4))))*M4:INDIRECT($N$1&ROW(4:4))*ISODD(COLUMN(M4:INDIRECT($N$1&ROW(4:4)))))You can try this formula in cell K4. The formula can be filled down to K5, K6 and so on. Only the column descriptions are entered in cells M1 and N1.
