Excel formula help please

[Participant]

Hi all

I have loads of spreadsheets with monthly data in, as below. I want to sum the year-to-date figures depending on the user entering the relevant month - 1 for Jan, 2 for Feb, etc. so that if the user entered "2", it would add January and February figures for each column to give me YTD data. I will have columns at the end of the data that have year-to-date totals.

I know about sum and choose, but I can't get it to work unless the cells are next to each other. For example, =SUM(CHOOSE,Month,B10:B20) would add the quantities sales and costs rather than just the quantities for each month.

Hopefully I've explained myself well enough. Many thanks, this will hopefully save me manually entering the monthly YTD figures for all my spreadsheets!

 

[Participant]

Thanks doogs, but if I've understood you correctly...I'm not sure that would work due to the fact that the cells I wish to add up are not next to each other, but separated by 9 cells.

If I used your formula, I'd be adding up quantity, sales and costs etc. when I would only want to add up all the quantities, all the sales, etc...

 

[Participant]

Thanks doogs.

I see how you're working your formula, and it's certainly a perfectly good workaround, so thanks for that.

I agree that there's probably a slicker method using arrays and maybe a DBASE function, but I can't work it out. Will try Mr. Excel, thanks again!