Drag the formula and change the COLUMN links instead of ROWS

Question

I have this formula in Excel, on line E5:

=SUM(Banco!H$5;Banco!H$6;Banco!H$8;Banco!H$9;Banco!H$10;Banco!H$11) 

I need it to change COLUMN links instead of ROWS when I drag it (basically it behaves as if I was dragging it) ... For example:

 =SUM(Banco!I$5;Banco!I$6;Banco!I$8;Banco!I$9;Banco!I$10;Banco!I$11) =SUM(Banco!J$5;Banco!J$6;Banco!J$8;Banco!J$9;Banco!J$10;Banco!J$11) =SUM(Banco!K$5;Banco!K$6;Banco!K$8;Banco!K$9;Banco!K$10;Banco!K$11) 

Any clues?

Thank you so much!

Answer 1

... Use the offset function.

For example - suppose you had a formula on line 1, and you would like to reference Banco!H5 , you could do something like:

 =OFFSET(Banco!$G$5,0,ROW()) 

Now, when you drag it down, it will be compensated by the number of lines down.

So your new formula is as follows:

 =SUM(OFFSET(Banco!$G$5,0,ROW()),OFFSET(Banco!$G$6,0,ROW()),OFFSET(Banco!$G$8,0,ROW()),OFFSET(Banco!$G$9,0,ROW()),OFFSET(Banco!$G$10,0,ROW()),OFFSET(Banco!$G$11,0,ROW())) 

Again, this assumes that you insert this formula in line 1 (I know this is ugly, but you specified specific cells, so you need to specify each separately)

Hope this makes sense

Answer 2

Use a combination of OFFSET and COUNTA functions. The OFFSET function gives the COUNTA column offset each time you go down. Make the county function count the number of lines above the line into which you drag the whole function (for example, every time you drag the function to an additional line, it adds 1)