changing column letter when dragging formula down

[kayy]

Hi! I saw a similar question posted, but couldn't figure out to apply it to my situation. The difference is I'm trying to do the sum on a another sheet. Please help!

I'd like to drag the formula down on my summary sheet, and only change the column letter and not the row number.

So looking at my image- I'd like Sheet1 in B6, to show =sum(jan!b4:b67). Then when I drag/copy that formula down to B7, I'd like for it to show =sum(jan!c4:c67).

Don't have to worry about insertions/deletions in the future. Thank you!!

 

[Peter_SSs]

Welcome to the MrExcel board!

I'm not sure how far across the 'Jan' sheet we need to allow for. I have gone to column Z but you can increase/decrease that as required.

Put this formula in cell B6 of 'Sheet1' and copy down.

=SUM(INDEX(Jan!$B$4:$Z$67,0,ROWS(B$6:B6)))

 

[kayy]

Thanks so much! Works beautifully!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.