Need help on copy a column data into another using formula

[younas]

Hi,

I am looking for a better solution to copy the data from one or more random columns to one fixed column by using a formula. I am currently doing it the way mentioned in attached sample file but it is not suitable if the range of columns expands as one has to manually write this long formula to new limit. Please help if there is any simple and more powerful option is available.

Thank in advance for your kind support.

 

[Nebu]

Hi :

You can use Sumproduct for this

 

[younas]

Hi Nebu,

Sumproduct doing wonder. thank you for the help.

 

[Nebu]

Ur welcome

 

[Ajesh]

Though Younas has figured it out, just for anyone else who stumble upon this post looking for similar help:

In cell A3 =SUMPRODUCT(($B3:$R3)*(B$1:R$1))
and then fill down the formula in column A.

Thanks