CODEWITHSUNDEEP
exceldrag
Alfred S.
Alfred S.

Reputation: 43

Excel: Drag right, move down

I've an cohort analysis of a customer base. Pretty simple: In the columns I've the months of becoming an initial customer, in the columns all following months. Values are #customers.

This naturally shapes a spreadsheet where as with every column you go right, data starts one line further down. (If I've customers since 2006 and look in the cohort of 2012 it's clear that there are empty cells until it's finally 2012).

Now I want to determine the sum of the first 12 months (rows) per column at the very bottom of the sheet. So I do something like

=SUM(A2:A13)

If i drag that formula to the right I've to jump into every cell and then drag the area down by 1 cell.

How can excel handle that for me?

enter image description here

Upvotes: 1

Views: 700

Answers (1)

Will BeDeleted
Will BeDeleted

Reputation: 87

=SUM(OFFSET(A2:A13,COUNTIF(A2:A999,""),0,12))

The countif finds the first non empty cell in the column, then offset then creates a 12 row range starting from that offset.

The sum uses the range for the calculation.

You might need to put the formula at the top of the columns if the data is "ragged" at the bottom

Upvotes: 1

Related Questions