Calculating the Percentage With Values That Change

[BuildingandFleet]

Good Morning,

Please help me figure out how to calculate the percentage in a group when the values change. So, I have three different types of vehicles (T, PM & W) and three different mileage categories (0-125k, 126k-150k & 151k-250k). My goal is to forecast the mileages based on last year's 6 month and 12 month average miles driven when I fill in the current mileage. I would like for the chart to show the different percentage of my total fleet as the mileages change from one category to another. I have used conditional formatting in order to separate the mileage categories by color.

Is this possible? TY!

I would like to have the following results:

% T green
% T yellow
% T red
% PM green
% PM yellow
% PM red
% W green
% W yellow
% W red

Mileage	6mth	12mth
T	5674	12974	27474
T	19712	27012	41512
PM	26584	33884	48384
PM	48289	55589	70089
PM	52344	59644	74144
T	56551	63851	78351
T	56934	64234	78734
T	67662	74962	89462
T	86735	94035	108535
T	87545	94845	109345
W	95614	102914	117414
T	96006	103306	117806
T	98900	106200	120700
T	106554	113854	128354
T	109584	116884	131384
T	130055	137355	151855
T	131055	138355	152855
PM	140804	148104	162604
PM	147932	155232	169732
PM	184317	191617	206117
PM	199241	206541	221041

 

[Jeffrey Mahoney]

Did this come close to what you need?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Mileage	6mth	12mth			Vehicle Type:	T	PM	W	T	PM	W	T	PM	W
2	T	5674	12974	27474			0k-125k	0k-125k	0k-125k	126k-150k	126k-150k	126k-150k	151k-250k	151k-250k	151k-250k
3	T	19712	27012	41512		6 Month:	791,857	127,217	95,614	261,110	288,736	0	0	383,558	0
4	PM	26584	33884	48384		12 Month:	872,157	149,117	102,914	261,110	140,804	0	0	531,490	0
5	PM	48289	55589	70089		6 Month:	40.65%	6.53%	4.91%	13.40%	14.82%	0.00%	0.00%	19.69%	0.00%
6	PM	52344	59644	74144		12 Month:	44.77%	7.65%	5.28%	13.40%	7.23%	0.00%	0.00%	27.28%	0.00%
7	T	56551	63851	78351
8	T	56934	64234	78734		Total 6 Month:	1,948,092
9	T	67662	74962	89462		Total 12 Month:	2,057,592
10	T	86735	94035	108535
11	T	87545	94845	109345		Total T 6 Month:	54.05%
12	W	95614	102914	117414		Total PM 6 Month:	41.04%
13	T	96006	103306	117806		Total W 6 Month:	4.91%
14	T	98900	106200	120700
15	T	106554	113854	128354		Total T 12 Month:	51.17%
16	T	109584	116884	131384		Total PM 12 Month:	38.86%
17	T	130055	137355	151855		Total W 12 Month:	4.65%
18	T	131055	138355	152855
19	PM	140804	148104	162604		Total 0k-125k:	53.40%
20	PM	147932	155232	169732		Total 126k-150k:	23.76%
21	PM	184317	191617	206117		Total 151k-250k:	22.84%
22	PM	199241	206541	221041
Sheet1

Cell Formulas
Range	Formula
G3:I3	G3	=SUMIFS($B$2:$B$22,$A$2:$A$22,G$1,$B$2:$B$22,"<"&126000)
J3:L3	J3	=SUMIFS($B$2:$B$22,$A$2:$A$22,J$1,$B$2:$B$22,">"&125000,$B$2:$B$22,"<"&151000)
M3:O3	M3	=SUMIFS($B$2:$B$22,$A$2:$A$22,M$1,$B$2:$B$22,">"&150000,$B$2:$B$22,"<"&251000)
G4:I4	G4	=SUMIFS($C$2:$C$22,$A$2:$A$22,G$1,$C$2:$C$22,"<"&126000)
J4:L4	J4	=SUMIFS($B$2:$B$22,$A$2:$A$22,J$1,$C$2:$C$22,">"&125000,$C$2:$C$22,"<"&151000)
M4:O4	M4	=SUMIFS($B$2:$B$22,$A$2:$A$22,M$1,$C$2:$C$22,">"&150000,$C$2:$C$22,"<"&251000)
G5:O6	G5	=G3/SUM($B$2:$B$22)
G8:G9	G8	=SUM(G3:O3)
G11	G11	=SUM(G3,J3,M3)/G8
G12	G12	=SUM(H3,K3,N3)/G8
G13	G13	=SUM(I3,L3,O3)/G8
G15	G15	=SUM(G3,J3,M3)/G9
G16	G16	=SUM(H3,K3,N3)/G9
G17	G17	=SUM(I3,L3,O3)/G9
G19	G19	=SUM(G3:I4)/SUM(G3:O4)
G20	G20	=SUM(J3:L4)/SUM(G3:O4)
G21	G21	=SUM(M3:O4)/SUM(G3:O4)

 

[BuildingandFleet]

Good Morning Jeffrey!

I really appreciate your help with my dilemma. Please correct me if I am wrong, but as I was going over the formulas, I changed the M4 formula to include column D. Originally, I was headed in the right direction with SUMIFS, but for someone like me who does not have many hours logged in Excel, I had no chance. Thank you again for your help with this and I hope you have a great day!