Sum of every 3rd column as per the month

[manojk71273]

Dear All

I have an excel sheet having data like below

Column A	B	C	D	E	F	G	H	I	J
	Apr				May			Jun
Particular	Name	G	NG	Total	G	NG	Total	G	NG

Now i want a total of Quarter to date and Year to date total on the basis of Month/s

if month is Q1 Apr then Column E , if Month is May then total of column E+H .... for Jun total of column E+H+K and every Qtr the cell reference will change.

for YTD in Apr E in May E+H, in Jun E+H+K in Jul it will be E+H+K+N and so on.......

how can I do it please help,

Thanks in advance.
Manojk.

 

[awoohaw]

part 3 of 4:

mr excel questions 17.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER	ES	ET	EU	EV	EW	EX	EY	EZ	FA	FB	FC	FD	FE	FF	FG	FH	FI	FJ	FK	FL	FM	FN	FO	FP	FQ	FR	FS	FT	FU	FV	FW	FX	FY	FZ	GA	GB	GC	GD	GE	GF	GG	GH	GI	GJ	GK	GL	GM	GN	GO	GP	GQ	GR	GS	GT	GU	GV	GW	GX	GY	GZ	HA	HB	HC	HD	HE	HF
11	Category7	Product3	0.01	0.02	0.03	0.01	0.02	0.03	0.01	0.02	0.03	0.01	0.02	0.03	0.01	0.02	0.03	0.01	0.02	0.03	0.01	0.02	0.03	0.01	0.02	0.03	0.01	0.02	0.03	0.01	0.02	0.03	0.01	0.02	0.03	0.01	0.02	0.03	0.12	0.24	0.36		0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.12	0.12	0.24		0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.12	0.12	0.24		0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.01	0.01	0.02	0.12	0.12	0.24		0.04	0.05	0.09	0.04	0.05	0.09	0.04	0.05	0.09	0.04	0.05	0.09	0.04	0.05	0.09	0.04	0.05	0.09	0.04	0.05	0.09	0.04	0.05	0.09	0.04	0.05	0.09	0.04	0.05	0.09	0.04	0.05	0.09	0.04	0.05	0.09	0.48	0.6	1.08		0.03	0.02	0.02	0.02	0.09	0.06	0.06	0.06	0.36	0.24	0.24	0.24
12		Grand Total	0.07	0.14	0.21	0.07	0.14	0.21	0.07	0.14	0.21	0.07	0.14	0.21	0.07	0.14	0.21	0.07	0.14	0.21	0.07	0.14	0.21	0.07	0.14	0.21	0.07	0.14	0.21	0.07	0.14	0.21	0.07	0.14	0.21	0.07	0.14	0.21	0.84	1.68	2.52	0	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.84	0.84	1.68	0	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.84	0.84	1.68	0	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.07	0.07	0.14	0.84	0.84	1.68	0	0.28	0.35	0.63	0.28	0.35	0.63	0.28	0.35	0.63	0.28	0.35	0.63	0.28	0.35	0.63	0.28	0.35	0.63	0.28	0.35	0.63	0.28	0.35	0.63	0.28	0.35	0.63	0.28	0.35	0.63	0.28	0.35	0.63	0.28	0.35	0.63	3.36	4.2	7.56		0.21	0.14	0.14	0.14	0.63	0.42	0.42	0.42	2.52	1.68	1.68	1.68
13
Sheet1

Cell Formulas
Range	Formula
E11,FB11,EY11,EV11,ES11,EP11,EM11,EJ11,EG11,ED11,EA11,DX11,DU11,DN11,DK11,DH11,DE11,DB11,CY11,CV11,CS11,CP11,CM11,CJ11,CG11,BZ11,BW11,BT11,BQ11,BN11,BK11,BH11,BE11,BB11,AY11,AV11,AS11,AL11,AI11,AF11,AC11,Z11,W11,T11,Q11,N11,K11,H11	E11	=C11+D11
AM11:AN11,FC11:FD11,DO11:DP11,CA11:CB11	AM11	=C11+F11+I11+L11+O11+R11+U11+X11+AA11+AD11+AG11+AJ11
AO11,FE11,DQ11,CC11	AO11	=SUM(AM11:AN11)
FG11:GS11	FG11	=+C11+AQ11+CE11+DS11
GU11:GX12	GU11	=INDIRECT(LEFT(TEXT(ADDRESS(5,(GU$2),4),0),2)&ROW(),TRUE)
GY11:GY12	GY11	=AF11+AI11+AL11
GZ11:GZ12	GZ11	=BT11+BW11+BZ11
HA11:HA12	HA11	=DH11+DK11+DN11
HB11:HB12	HB11	=EV11+EY11+FB11
HC11:HC12	HC11	=E11+H11+K11+N11+Q11++T11+W11+Z11+AC11+AF11+AI11+AL11
HD11:HD12	HD11	=AS11+AV11+AY11+BB11+BE11++BH11+BK11+BN11+BQ11+BT11+BW11+BZ11
HE11:HE12	HE11	=CG11+CJ11+CM11+CP11+CS11++CV11+CY11+DB11+DE11+DH11+DK11+DN11
HF11:HF12	HF11	=DU11+DX11+EA11+ED11+EG11++EJ11+EM11+EP11+ES11+EV11+EY11+FB11
C12:GS12	C12	=SUM(C5:C11)

 

[awoohaw]

This section should first be put in your cell A14 to start with and to see how the formulas work:

mr excel questions 17.xlsm
	A	B	C	D	E	F
14	Category	Sale	Product	Qtr	NG/G/TOTAL	Amount
15	Category1	Sale1	Product1	Apr	NG	0.01
16	Category1	Sale1	Product1	Apr	G	0.02
17	Category1	Sale1	Product1	Apr	Total	0.03
18	Category1	Sale1	Product1	May	NG	0.01
19	Category1	Sale1	Product1	May	G	0.02
20	Category1	Sale1	Product1	May	Total	0.03
21	Category1	Sale1	Product1	Jun	NG	0.01
22	Category1	Sale1	Product1	Jun	G	0.02
23	Category1	Sale1	Product1	Jun	Total	0.03
24	Category1	Sale1	Product1	Jul	NG	0.01
25	Category1	Sale1	Product1	Jul	G	0.02
26	Category1	Sale1	Product1	Jul	Total	0.03
27	Category1	Sale1	Product1	Aug	NG	0.01
28	Category1	Sale1	Product1	Aug	G	0.02
29	Category1	Sale1	Product1	Aug	Total	0.03
30	Category1	Sale1	Product1	Sep	NG	0.01
31	Category1	Sale1	Product1	Sep	G	0.02
32	Category1	Sale1	Product1	Sep	Total	0.03
33	Category1	Sale1	Product1	Oct	NG	0.01
34	Category1	Sale1	Product1	Oct	G	0.02
Sheet1

Cell Formulas
Range	Formula
A15:A34	A15	=INDEX($A$5:$A$11,INT(((ROW(A15)-15)+1)/199)+1,1)
B15	B15	=IF(INDEX(SUBSTITUTE($C$2:$HF$2," ",""),1,MOD((ROW(A15)-15),199)+1)="",B14,INDEX(SUBSTITUTE($C$2:$HF$2," ",""),1,MOD((ROW(B15)-15),199)+1))
C15:C34	C15	=INDEX($B$5:$B$11,INT(((ROW(C15)-15)+1)/199)+1,1)
D15:D34	D15	=IF(INDEX(SUBSTITUTE($C$3:$HF$3," ",""),1,MOD((ROW(D15)-15),199)+1)="",D14,INDEX(SUBSTITUTE($C$3:$HF$3," ",""),1,MOD((ROW(D15)-15),199)+1))
E15:E34	E15	=INDEX(SUBSTITUTE($C$4:$HF$4," ",""),1,MOD((ROW(E15)-15),199)+1)
F15:F34	F15	=INDEX(SUBSTITUTE($C$5:$HF$11," ",""),INT(((ROW(F15)-15)+1)/199)+1,MOD((ROW(F15)-15),199)+1)
B16:B34	B16	=IF(INDEX(SUBSTITUTE($C$2:$HF$2," ",""),1,MOD((ROW(A16)-15),199)+1)="",B15,INDEX(SUBSTITUTE($C$2:$HF$2," ",""),1,MOD((ROW(A16)-15),199)+1))

 

[awoohaw]

So, in the above, the fourth mini workbook is the critical one as it creates the flat file.
The top three are just the data I used. If you want you make you own data up (for testing)... but.. you must keep the header rows as they are the first of the mini workbooks.

 

[manojk71273]

no worries, thanks for your replies and valuable time.

 

[awoohaw]

no worries, thanks for your replies and valuable

You're welcome. I hope you are able to use what I've done.