Sum to month value

[Ned Elp]

I have a spread sheet with data against months and what I want to do is to return a cumulative figure for a given month. I.e. if I choose "Apr" from a dropdown list, then I'll get the cumulative figure for the year up to April (Jan-Apr). I bet it's a simple formula but it's escaping me. I don't want to go down the route of creating a seperate dataset for cumulative figures =Sum($A$1:A1) as it'll take a long time because of the size of the spread sheet. Want to avoid VBA too if possible.

Any help on this would be greatly appreciated.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Month
[/TD]
[TD]Data 1
[/TD]
[TD]Data2
[/TD]
[TD]Data 3
[/TD]
[/TR]
[TR]
[TD]Jan
[/TD]
[TD]123
[/TD]
[TD]456
[/TD]
[TD]789
[/TD]
[/TR]
[TR]
[TD]Feb
[/TD]
[TD]231
[/TD]
[TD]564
[/TD]
[TD]897
[/TD]
[/TR]
[TR]
[TD]Mar
[/TD]
[TD]312[/TD]
[TD]645
[/TD]
[TD]978[/TD]
[/TR]
[TR]
[TD]Apr
[/TD]
[TD]123
[/TD]
[TD]456
[/TD]
[TD]789
[/TD]
[/TR]
[TR]
[TD]...
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[/TR]
[TR]
[TD]Dec
[/TD]
[TD]312
[/TD]
[TD]645
[/TD]
[TD]978
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[cyrilbrd]

Given in A1:

Excel 2010
	A	B	C	D
1	Month	Data1	Data2	Data3
2	Jan	123	456	789
3	Feb	231	564	897
4	Mar	312	645	978
5	Apr	123	456	789
6	May	288	143	331
7	Jun	335	163	262
8	Jul	253	234	298
9	Aug	267	191	215
10
11
12	month	data	sum
13	Apr	Data3	3453
Sheet2

Conditions:
Months are DATES formatted as month, eg Jan is 1/1/2014 formatted as [$-409]mmm;@
Cell A13 is data validation / list use the range A2:A9, thus will allow you to choose the month of preference form the list, limiting the chances of typo.
Cell B13 can follow same method as above with range B1:D1
Formula in C13 is =SUM(INDIRECT(ADDRESS(2,MATCH($B$13,$A$1:$D$1))&":"&ADDRESS(MATCH(A13,A2:A9)+1,MATCH($B$13,$A$1:$D$1))))

Would that work for you?

 

[Ned Elp]

Hi Cyrilbrd,

Thanks for the quick response. That's exactly what I was looking for, well done on understanding my question (I re-read it and it didn't make sense to me!!!)
One thing I forgot to mention is that the data is on one work sheet and I want to display the data on another which is extracted and uploaded into a dashboard.

I've never used ADDRESS or INDIRECT before and when I'm manipulating the formula I'm getting an #N/A error (I think from the ADDRESS part of the formula)
Also, the dropdown menu is on one sheet, the data another and the display data yet another all within the same workbook.

Thanks in advance for any further help.

 

[cyrilbrd]

Hi Cyrilbrd,

Thanks for the quick response. That's exactly what I was looking for, well done on understanding my question (I re-read it and it didn't make sense to me!!!)

Most welcome, glad it fits your requirement.

One thing I forgot to mention is that the data is on one work sheet and I want to display the data on another which is extracted and uploaded into a dashboard.

I've never used ADDRESS or INDIRECT before and when I'm manipulating the formula I'm getting an #N/A error (I think from the ADDRESS part of the formula)
Also, the dropdown menu is on one sheet, the data another and the display data yet another all within the same workbook.

Thanks in advance for any further help.

If in the first sheet (named here 'data') you have the raw data.

Excel 2010
	A	B	C	D
1	Month	Data1	Data2	Data3
2	Jan	123	456	789
3	Feb	231	564	897
4	Mar	312	645	978
5	Apr	123	456	789
6	May	288	143	331
7	Jun	335	163	262
8	Jul	253	234	298
9	Aug	267	191	215
data

and wish to have the result is sheet 'results'

Excel 2010
	A	B	C
1	month	data	sum
2	Aug	Data3	4559
results

Formula in C2 is =SUM(INDIRECT("'data'!"&ADDRESS(2,MATCH($B$2,data!$A$1:$D$1))&":"&ADDRESS(MATCH(A2,data!$A$2:$A$9,0)+1,MATCH($B$2,data!$A$1:$D$1))))
Note drop down menu will refer to =data!$A$2:$A$9 and =data!$B$1:$D$1