change column letter in formula with drag down

[rubertu_22]

Hello,

This is the formula
=SUM(B12+B16+B26+SUM(B27:B33)

What i've been trying to do is as I drag down the column to have the formula change to =SUM(c12+c16+c26+SUM(c27:c33) and so on.

Basically is what happens we drag to the right we get an increment on the column letter, but I want it when I drag down

 

[Richard Schollar]

Well, you could with this:

=SUM(INDEX($1:$33,12,ROWS($A$1:$A1)-ROW($A$1)+2),INDEX($1:$33,16,ROWS($A$1:$A1)-ROW($A$1)+2),INDEX($1:$33,26,ROWS($A$1:$A1)-ROW($A$1)+2):INDEX($1:$33,33,ROWS($A$1:$A1)-ROW($A$1)+2))

 

[Peter_SSs]

I think this should also do it and should be more robust in relation to rows subsequently inserted/deleted near the top of the sheet.

Replace B$37 and B37 in the formula with the corresponding address of the first formula cell before dragging down.

=INDEX($12:$12,ROWS(B$37:B37)+1)+INDEX($16:$16,ROWS(B$37:B37)+1)+SUM(INDEX($26:$33,0,ROWS(B$37:B37)+1))

Edit: This still isn't robust against column insertion/deletion though.

 

[Peter_SSs]

I think this should be better

=INDEX($12:$12,COLUMN($B$12)+ROWS(B$37:B37)-1)+INDEX($16:$16,COLUMN($B$12)+ROWS(B$37:B37)-1)+SUM(INDEX($26:$33,0,COLUMN($B$12)+ROWS(B$37:B37)-1))

 

[jasonb75]

How about

=SUM(SUBTOTAL(9,OFFSET($A$12,{0,4,14},ROWS($1:1),{1,1,8},1)))

Not robust against row insertion in the current format.

 

[Peter_SSs]

How about

=SUM(SUBTOTAL(9,OFFSET($A$12,{0,4,14},ROWS($1:1),{1,1,8},1)))

Not robust against row insertion in the current format.

Also not robust for deletion of column A or insertion of a new column B but I think both of those could be overcome as follows. Assuming the first formula is in cell D37

=SUM(SUBTOTAL(9,OFFSET($B$12,{0,4,14},ROWS(D$37:D37)-1,{1,1,8},1)))

The only other issue is dependant on how far this is being copied since OFFSET is a volatile function

 

[jasonb75]

There was another method I was thinking of Peter, but as with anything, insetring / deleting rows / columns can be the biggest problem to overcome.

That was to sum left to right in a hidden row, then array transpose the results to the required column.

 

[rubertu_22]

Your help is great, but still I can't get it to work, it's purely to automate the rest of the formulas of the column by dragging down.
Example of what I've.


____A______B_______C______D_______...______P__________Q
1________|Janeiro_|Fevreiro|Março___|______Control________
2Some tag|_______|_______|345____|______ =Sum(b4+b8)__Janeiro
3________|_______|_______|234____|______ =Sum(c4+c8)__Fevreiro
4Total_1__|100____|_______|579____|______ =Sum(d4+d8)__Março
5________|_______|_______|_______|
6________|_______|2345___|_______|
7________|33234__|_______|_______|
8Total 2__|33234__|_______|_______|
9________|_______|_______|_______|
10_______|_______|_______|_______|

 

[rubertu_22]

So on the column "p" I just want to drag the 1st expression and the column letter to increase by 1; b -> c, c-> d, the numbers(rows) stay the same

 

[jasonb75]

Have you tried the formula and checked the results?

You will NOT see the column change as you drag the formula down, the solutions that have been provided use the increasing row to find the correct column relative to the starting point.