Vlookup/Sumif/or CSE formula quandry?

[RickM]

I am trying to return sum of quarterly data to another cell. Looking for a formula to return the sum of 11175 colC (120+117+234) in another cell and then (261+253+307) etc. Will need that for each ColumnC thru ColumnI. The Date will change each month. Next month the first date will be Feb/2010. So I am trying to get the first three cells in ColC and the next 3 cells in ColC. Not sure which formula to use??????

Excel Workbook
	A	B	C	D	E	F	G	H	I
2	Branch #	Date	A	B	C	D	E	F	N
3	11175	Jan/2010	120	10	19	18	10	38	70
4	11175	Feb/2010	117	11	23	17	10	75	70
5	11175	Mar/2010	234	22	14	20	10	90	141
6	11175	Apr/2010	261	34	30	27	10	86	137
7	11175	May/2010	253	36	19	23	10	59	116
8	11175	Jun/2010	307	35	51	25	12	83	142
9	11175	Jul/2010	298	24	42	25	10	42	110
10	11175	Aug/2010	219	19	15	19	18	54	111
11	11175	Sep/2010	85	16	15	8	7	27	63
12	11175	Oct/2010	65	6	1	2	3	15	42
13	11175	Nov/2010	86	1	14	9	2	27	58
14	11175	Dec/2010	223	25	25	32	12	85	152
15	11175	Jan/2011	235	30	26	18	10	77	125
16	11200	Jan/2010	26	2	1	1	1		14
17	11200	Feb/2010	37	1	2	1	2	2	14
18	11200	Mar/2010	41	2	1	1		1	21
19	11200	Apr/2010	28		2	2	1	2	15
20	11200	May/2010	33	2	2	3	1	6	14
21	11200	Jun/2010	34	1		2	2	1	33
22	11200	Jul/2010	29	3	2	1		3	16
23	11200	Aug/2010	51	4			1	3	24
24	11200	Sep/2010	45	2	2	2	1	7	37
25	11200	Oct/2010	54	3	3	2	3	7	49
26	11200	Nov/2010	44	3	3	2		3	22
27	11200	Dec/2010	35			2			24
28	11200	Jan/2011	37	2		4		3	15
DATA

Excel 2007

 

[Haseeb Avarakkan]

Don't know why. May be your Branch # in Data tab & Lookup Branch in A39 are in different format.

Try this,

=SUMPRODUCT((DATA!$A$3:$A$752&""=$A$39&"")*(YEAR(DATA!$B$3:$B$752)=$A$42)*(DATA!$B$3:$B$752 < DATE($A$42,(3*ROWS(G$39:G39))+1,0))*INDEX(DATA!$C$3:$I$752,0,MATCH(G$38,DATA!$C$2:$I$2,0)))-SUM(G$38:G38)

 

[RickM]

Yes it was a format issue. It works now. This works for a single year, but what is happening is the data file is a rolling 13 months of data. So the year will over lap. What would the formula be for G43 if that is the beginning of 2011?

 

[Haseeb Avarakkan]

Try this;

G39, copy across & down...

=SUMIFS(INDEX(DATA!$C$3:$I$752,0,MATCH(G$38,DATA!$C$2:$I$2,0)),DATA!$A$3:$A$752,$A$39,DATA!$B$3:$B$752,">="&DATE($A$42,(3*ROWS(G$39:G39)-2),1),DATA!$B$3:$B$752,"<="&DATE($A$42,(3*ROWS(G$39:G39)+1),0))

Also Pivot table is better.

 

[RickM]

That seems to be working.... I will know more after I import more data.
Thanks.