Excel formula help please

Participant · 12 Mar 2010 at 11:32

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!

doogs · 12 Mar 2010 at 13:09

Hi,
I have (sort of) a solution...

=SUM(E11:CHOOSE(($D$7-1),E12,E13,E14,E15,E16,E17,E18,E19,E20,E21,E22))

where E11 is the start of my column of data (say January's) through to E22 (which is December.
Cell D7 is where you would enter the month for YTD total (e.g., enter 3 if you wished to sum January to March). The only problem is getting a sum for just January, in that you would get an error (the minus 1 in the above formula has been included to get it to add the column correctly at the moment.

Sorry if teh above sounds complicated - it was a quick and dirty look at the formula.

Cheers,
Doogs

Participant · 12 Mar 2010 at 14:20

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...

doogs · 16 Mar 2010 at 11:00

Hi,
Apologies for not coming back sooner - I now udnerstand the problem better.
Here's a possible solution (not very elegant but, I think it works)...

=(SUM(C5

CHOOSE($C$2,C5,G5,K5,O5))))-(SUM(D5:F5))-(SUM(H5:J5))-(SUM(L5:N5))

Where:
C2 is the cell for entering the month for YTD calculation
C5, G5, K5 and O5 are the cells with data I wish to add (in this example 4 values, 4 cells apart)
The minus sums D5 to F5, H5 to J5 and L5 to N5 just cancel out the values inbetween the Cells that we really want to add.

I'm sure there's a much better solution with Lookups or arrays of something but, I'm no expert (as you can probably tell).

The other thing to do is post your query on mrexcel.com - they've always helped me in the past.

Cheers,
Doogs

Participant · 16 Mar 2010 at 11:20

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!

doogs · 16 Mar 2010 at 11:26

No problem. If you get a better answer can you post it here (out of curiosity).
I also see my above post has a smilie in it somehow (should be a colon followed by an opening bracket).

Cheers,
Doogs

Malakite · 16 Mar 2010 at 11:34

I cant view the screen you posted due to my dodgy work security but if you want to email me a sample of the sheet I can probaly help. I will email you my email address to your trust if you want to.

From what's explained I would do it using a couple of different elements.

First having a table of dates to values. So 1 to 12 in a column then the month end date beside it. so 31/01/10 beside the 1 etc you can the just autofill this down to get the end date for every month..
Then a vlookup pointing to the value your user enters.

=VLOOKUP(J3,$A$3:$B$14,2,FALSE) with J3 being where they enter the 1 or 2 etc and the range being your lookup table. this gives you the end date based on your user entry. As your using year to date I assume all start dates will be 01/01/10.

Then just use a sumproduct for you data columns.

=SUMPRODUCT(($E$3:$E$14>=1/1/10)*($E$3:$E$14<=$J$6)*($F$3:$G$14))

where the E colums and your dates to the right of your data, the J cell being your lookup formula and the F:G your actual data, expand this as needed.

Could be made a lot easier using a combo box for your user to choose the date neede and this indexed into the sumproduct.