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

 

[Gerald Higgins]

Maybe I'm missing something, but why not just use

=sum(C3:C5)

?

 

[RickM]

I want to have a drop down that will list the branch # as the value to lookup. The return the first 3 rows and then the next 3 rows. I am summarizing rolling quarter numbers.

 

[stefankemp]

Quarter 2010 2011 2012
1 *
2
3
4

Add a table like above with the top left in cell K2 and enter the following formula where the * is (cell L3):

=SUMPRODUCT(($A$3:$A$28=11175)*(YEAR($B$3:$B$28)=G$3)*(INT((MONTH($B$3:$B$28)-1)/3)+1=$F2)*$C$3:$C$28)

Do a separate table for each of columns C to I.

 

[Gerald Higgins]

Ah, that changes things.

Maybe this for the first quarter.
Assumes the first instance of 11175 is in cell A3.
Assumes your drop down to select the branch # is in cell K4.

=SUM(OFFSET(A3,MATCH(K4,A3:A28,0)-1,2,3,1))

This works on the data sample you posted.

You should be able to work out the formula for the second quarter from this.
If not, check if this works for quarter 1, and then post back.

 

[RickM]

Gerald, I am getting the dreaded #N/A with that formula? I don't understand the -1,2,3,1)) part.

 

[Gerald Higgins]

Have you put something into K4 ?
Like 11175, or 11200 ?

If we can get this to work, I'll explain how it works.

 

[RickM]

I got it to work. It is a format issue. Coulmn A branch number is actually a linked table from Access. If I copy the value of A3 (11175) in K4 it works. When I try and copy Column A and Paste Special the formats into Column K it still does not work. Column A is formatted as "general". Thanks I am getting close.

 

[Gerald Higgins]

Great, do you still need help ?

 

[RickM]

Yes....What do I change in the formula to get same results for column D? then I can duplicate that across the columns?