Formula required to reflect change of month in SUM calc

[MarcB]

I have a table with locations in rows and days (in date format) across columns, with daily data. I am trying to build another table below which uses the daily data above, but has cumulative month-to-date totals, using formulae I can copy across the new table, which recognises the change in the month in order to change the starting point of the SUM range. Any suggestions?

 

[sanrv1f]

Assuming your data look like this

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		01-Jan	04-Jan	07-Jan	10-Jan	13-Jan	16-Jan	19-Jan	22-Jan	25-Jan	28-Jan	31-Jan	03-Feb	06-Feb	09-Feb	12-Feb	15-Feb
2	North	96	35	1	97	30	14	5	35	70	97	82	57	23	33	6	77
3	East	18	97	6	65	10	95	22	49	41	2	67	44	38	11	85	10
4	South	1	21	69	16	9	87	86	42	20	28	6	59	33	25	47	73
5	West	31	11	47	94	55	33	77	39	34	59	53	84	83	75	8	20
Sheet1

Excel 2003

If the order of locations, is same both tables then you this

Excel Workbook
	A	B	C
7		Jan-11	Feb-11
8	North	562	196
9	East	472	188
10	South	385	237
11	West	533	270
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B8	=SUMIF($B$1:$Q$1,"<="&EOMONTH(B$7,0),$B2:$Q2)-SUMIF($B$1:$Q$1,"<"&A$7,$B2:$Q2)

if the Location order is different, then

Excel Workbook
	F	G	H
7		Jan-11	Feb-11
8	East	562	196
9	West	472	188
10	South	385	237
11	North	533	270
Sheet1

Excel 2003

Cell Formulas
Range	Formula
G8	=SUMPRODUCT((TEXT($B$1:$Q$1,"mmm-yy")+0=G$7)*($A$2:$A$5=$A8),$B$2:$Q$5)

 

[MarcB]

Thanks Sankar, but I need the corresponding month to date (MTD) figure for each of the days, so my table below would be the exact same layout as the table above with daily data, except the data would be the MTD at that particular day, if that makes sense.

 

[sanrv1f]

ok,try this

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
15		01-Jan	04-Jan	07-Jan	10-Jan	13-Jan	16-Jan	19-Jan	22-Jan	25-Jan	28-Jan	31-Jan	03-Feb	06-Feb	09-Feb	12-Feb	15-Feb
16	North	96	131	132	229	259	273	278	313	383	480	562	57	80	113	119	196
17	East	18	115	121	186	196	291	313	362	403	405	472	44	82	93	178	188
18	South	1	22	91	107	116	203	289	331	351	379	385	59	92	117	164	237
19	West	31	42	89	183	238	271	348	387	421	480	533	84	167	242	250	270
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B16	=SUMIF($B$1:$Q$1,"<="&B$15,$B2:$Q2)-SUMIF($B$1:$Q$1,"<="&EOMONTH(B$15,-1),$B2:$Q2)

 

[MarcB]

Perfect! Thank you.