Forum Discussion
Dynamic Column cell reference with autofill
Hi,
I want to increase the column reference in a formula by 2 columns as it is copied across columns.
So in Column A the formula is =SUM(D3:E3)
When I fill this across Column B I want the formula to be =SUM(F3:G3)
I have experimented with the INDEX and COLUMN functions but not been able to get the intended result.
Provided you are on a modern Excel version you can try this:
=TRANSPOSE(BYROW(WRAPROWS(D3:S3,2),SUM))WRAPROWS creates an array of values from column pairs (D and E, F and G and so on).
BYROW then sums these pairs on by one into a 1-column array.
TRRANSPOSE that result into a 1-row array.
Change the range D3:S3 to the actual range that contains your data.
1 Reply
Provided you are on a modern Excel version you can try this:
=TRANSPOSE(BYROW(WRAPROWS(D3:S3,2),SUM))WRAPROWS creates an array of values from column pairs (D and E, F and G and so on).
BYROW then sums these pairs on by one into a 1-column array.
TRRANSPOSE that result into a 1-row array.
Change the range D3:S3 to the actual range that contains your data.