How to summarize data across sheets with dynamic & irregular ranges.

[mrxlsx]

Hello all,

I have a query about summarizing data across sheets.

I have my data in 12 sheets named by the month names and a Summary sheet. I have to summarize the data in the Summary sheet.


The columns in all the sheets are Product, Qty for 4 regions, Sales for 4 regions, Commission for 4 regions.


Each month Sales varies. That's the problem for me to use consolidate feature.


For detailed information please download the sample file below.


JCB.xlsx - Speedy Share - upload your files here

Thank you

Regards
mrxlsx

 

[hiker95]

mrxlsx,

Thanks for the workbook.

Then in a copy of your workbook, try the following to see if I have done this correctly:


If you were to add to the Jan thru Dec worksheets the following:

Excel Workbook
	U	V	W	X
2	East	West	North	South
3	408	503	377	442
4	371	372	333	170
5	463	475	409	321
6	533	503	481	416
7	189	86	146	219
8	367	257	279	289
9	227	222	272	220
10	396	378	289	437
11	317	301	358	333
12	246	370	247	410
13	290	401	290	443
14
Jan

The headings in cells U2:X2, and the formulae in cells, copied down:
U3
=SUMIF($B:$B,$Q3,C:C)

V3
=SUMIF($B:$B,$Q3,D:D)

W3
=SUMIF($B:$B,$Q3,E:E)

X3
=SUMIF($B:$B,$Q3,F:F)



Then in worksheet Summary (copy the below formulae into their respective cells):

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		Qty				Sales				Commission
2	Product	East	West	North	South	East	West	North	South	East	West	North	South			Product	Rate	Commision
3	Locomotives	4,578	3,980	4,408	3,925	7,100,478	6,172,980	6,836,808	6,087,675	1,214,628	1,055,967	1,169,524	1,041,375			Tractors	1,277	3%
4	Back Hoes	2,495	2,831	2,629	2,802	4,880,148	3,017,846	2,802,514	2,986,932	1,169,584	497,097	461,627	492,004			Trucks	505	9%
5	Excavators	5,144	4,712	5,012	5,067	3,818,052	3,929,808	4,180,008	4,225,878	766,448	424,196	451,203	456,155			Excavators	834	11%
6	Cranes	3,949	4,337	4,295	4,366	4,706,184	4,458,436	4,415,260	4,488,248	1,239,283	778,154	770,618	783,357			Back Hoes	1,066	16%
7	Oil Rigs	4,856	4,266	4,540	4,826	41,325,606	38,509,182	40,982,580	43,564,302	852,057	4,621,102	4,917,910	5,227,716			Rock Drillers	2,445	5%
8	Trucks	4,259	4,151	3,975	3,977	2,311,890	2,096,255	2,007,375	2,008,385	639,043	188,663	180,664	180,755			Oil Rigs	9,027	12%
9	Buses	3,562	3,722	3,221	3,805	3,964,548	3,223,252	2,789,386	3,295,130	1,180,431	535,855	463,726	547,804			Locomotives	1,551	17%
10	Torpedos	4,064	4,209	4,313	3,978	3,872,988	3,560,814	3,648,798	3,365,388	1,264,180	633,973	649,637	599,179			Buses	866	17%
11	Road Pavers	4,310	4,513	4,404	4,817	3,360,252	3,312,542	3,232,536	3,535,678	852,057	397,505	387,904	424,281			Torpedos	846	18%
12	Tractors	3,921	4,279	4,046	4,060	5,846,106	5,464,283	5,166,742	5,184,620	213,014	163,928	155,002	155,539			Cranes	1,028	17%
13	Rock Drillers	4,066	3,891	4,204	3,939	11,193,210	9,513,495	10,278,780	9,630,855	361,160	483,897	522,822	489,866			Road Pavers	734	12%
14		45,204	44,891	45,047	45,562	92,379,462	83,258,893	86,340,787	88,373,091	9,751,885	9,780,336	10,130,638	10,398,031
15
Summary

 

[hiker95]

mrxlsx,


Here is the other part of worksheet Summary:

Excel Workbook
	N	O	P	Q	R
1
2			Product	Rate	Commision
3			Tractors	1,277	3%
4			Trucks	505	9%
5			Excavators	834	11%
6			Back Hoes	1,066	16%
7			Rock Drillers	2,445	5%
8			Oil Rigs	9,027	12%
9			Locomotives	1,551	17%
10			Buses	866	17%
11			Torpedos	846	18%
12			Cranes	1,028	17%
13			Road Pavers	734	12%
14
15
Summary

 

[mrxlsx]

Thank you very much for everybody who got their sleeves rolled up for me.

I will check these options and come back. Thanks once again.


regards
mrxlsx.