Extracting data help

[Noxqss38242]

I have 2 tabs, one tab is my raw data. The other tab is my configured data. Here is a snap of my configured data:

MPG Report with Idle.xlsx
	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
1				Jan			Feb			Mar			Apr			May			Jun			Jul			Aug			Sep
2	Unit#	Make	Engine	MPG	Idle		MPG	Idle		MPG	Idle		MPG	Idle		MPG	Idle		MPG	Idle		MPG	Idle		MPG	Idle		MPG	Idle
3	20000-20049	Freightliner	2016 Detroit DD15	6.11	48.7		6.42	37.8
4	20050-20099	Freightliner	2016 Detroit DD15	6.26	44.8		6.02	36.2
5	20100-20249	Freightliner	2017 Detroit DD15	6.74	36.5		6.74	34.3		6.93	30.5		6.98	27.5		6.92	35.7
6	20250	Freightliner	2018 Detroit DD15	6.60	4.7		6.28	2.8		6.94	4.1		7.40	4.5		7.36	4.4
7	20251-20350	Freightliner	2018 Detroit DD15	6.89	45.3		6.88	41.1		7.14	36.7		7.17	39.1		6.72	41.3
8	20351-20425	Freightliner	2018 Detroit DD15	6.94	29.8		6.99	28.9		7.26	24.7		7.29	27.6		6.86	30.4
9	20426-20500	Freightliner	2018 Detroit DD13	6.89	39		6.12	40.3		7.12	36.7		7.06	39.6		7.07	42.6
10	20501-20625	Freightliner	2019 Detroit DD15	7.07	35.7		7.15	34		7.29	34.7		7.30	37.1		7.25	41.9
11	20626-20850	Freightliner	2019 Detroit DD13	7.06	38.6		7.12	36.3		7.30	35.6		7.31	38.5		7.36	42.8
12	20851-20875	Freightliner	2019 Detroit DD13	7.20	27.1		7.23	27.3		7.42	26.5		7.47	26.3		7.47	34.0
13	20876-20986	Freightliner	2020 Detroit DD13										7.39	26.5		7.51	24.7
14
17	16700-16799	International	2018 Cummins	6.47	46.4		6.58	44.1		6.77	42.3		6.74	47.0		6.74	50.8
18	16800-16899	International	2018 Cummins	6.55	46.9		6.62	44.2		6.71	43.1		6.80	44.5		6.79	49.0
19	16900-16949	International	2019 Intl A26	6.67	35.5		6.67	35.3		6.87	32.9		6.80	36.8		6.63	40.8
20	16950-16981	International	2020 Intl A26							6.96	29.0		6.94	33.5		6.80	39.8
21
22
23		Freightliner	2016 Detroit DD15	6.22	46.2		6.14	36.6
24		Freightliner	2017 Detroit DD15	6.74	36.5		6.74	34.3		6.93	30.5		6.98	27.5
25		Freightliner	2018 Detroit DD15	6.91	38.3		6.93	35.7		7.20	31.2		7.23	33.8
26		Freightliner	2018 Detroit DD13	6.89	39.0		6.12	40.3		7.12	36.7		7.06	39.6
27		Freightliner	2019 Detroit DD15	7.07	35.7		7.15	34.0		7.29	34.7		7.30	37.1
28		Freightliner	2019 Detroit DD13	7.07	37.4		7.13	35.4		7.31	34.7		7.33	37.3
29		Freightliner	2020 Detroit DD13										7.39	26.5
30
31		International	2018 Cummins	6.51	46.6		6.60	44.2		6.75	42.7		6.77	45.8
32		International	2019 Intl A26	6.67	35.5		6.67	35.3		6.87	32.9		6.80	36.8
33		International	2020 Intl A26							6.96	29.0		6.94	33.5
34
35			International	6.55	44.4		6.61	42.3		6.80	39.3		6.79	42.7		6.74	46.3
36			Freightliner	6.95	37.6		6.91	36.0		7.22	33.4		7.23	34.8		7.08	38.3
37			Fleet	6.86	39.2		6.84	37.1		7.10	35.0		7.11	36.9		6.99	40.5
2020

Here is a sample of my raw data, which again is just a sample of it. As you can see in my configured data, there are "unit# ranges" so the unit numbers go from 16700 to 20986.
I need on the configured data tab in cell P5 to be the sum of the unit range's miles divided by the sum of the unit range's gallons (I already did this manually in the snapshot) (This range is located in cells A3 to A20)
I need on the configured data tab in cell Q5 to be the average of the unit range's idle percentage. (I already did this manually in the snapshot)

I'm guessing from there once I have a formula that works, I can plug in the unit # ranges for cells P6, P7, P8, etc...

Stracs May20.xlsx
	A	B	C	D	E	F
1	Unit	Miles	Hours	Gallons	MPG	Idle
2	16700	9,449	402:03	1,416	6.67	52.5%
3	16701	10,910	512:13	1,623	6.72	59.2%
4	16702	12,788	280:39	1,638	7.81	11.2%
5	16703	9,494	315:11	1,386	6.85	38.9%
6	16704	12,182	717:10	1,940	6.28	67.8%
7	16705	17,536	505:34	2,422	7.24	31.9%
8	16706	8,790	570:44	1,375	6.39	68.5%
9	16707	6,727	258:21	1,031	6.53	47.7%
10	16708	7,287	257:35	940	7.75	40.0%
11	16709	8	006:26	7	1.08	0.0%
12	16710	8,762	448:19	1,230	7.13	59.4%
13	16711	8,154	455:12	1,178	6.92	65.5%
14	16712	8,373	414:46	1,387	6.04	58.8%
15	16713	4,996	295:16	830	6.02	63.9%
16	16714	11,617	339:08	1,621	7.17	32.1%
17	16715	5,003	423:29	903	5.54	72.6%
18	16716	7,207	442:29	1,143	6.31	68.4%
19	16717	16,114	643:00	2,273	7.09	51.3%
20	16718	7,164	202:33	968	7.40	24.3%
21	16719	9,012	478:41	1,401	6.43	61.1%
22	16720	12,357	267:44	1,744	7.08	11.0%
23	16721	11,286	261:04	1,484	7.60	20.7%
24	16722	11,037	528:27	1,673	6.60	57.9%
25	16723	8,567	292:54	1,170	7.32	42.5%
26	16724	6,548	234:41	926	7.07	40.2%
27	16725	8,952	457:19	1,412	6.34	59.6%
28	16726	4,312	321:26	730	5.91	73.4%
29	16727	10,231	668:43	1,575	6.50	70.1%
30	16728	18,144	708:58	2,500	7.26	51.3%
31	16729	1	000:25	1	1.33	0.0%
32	16730	7,967	169:31	987	8.07	6.5%
33	16731	8,471	480:32	1,301	6.51	66.1%
34	16732	9,924	436:20	1,432	6.93	56.7%
35	16733	8,220	544:44	1,426	5.77	66.9%
36	16734	6,867	391:04	1,090	6.30	60.9%
37	16735	12,020	504:22	1,812	6.63	54.5%
38	16736	9,780	555:29	1,495	6.54	62.6%
39	16737	8,762	378:48	1,294	6.77	55.4%
40	16738	9,285	308:50	1,244	7.46	36.8%
41	16739	6,291	323:12	989	6.36	62.2%
42	16740	13,488	298:53	1,826	7.39	9.6%
43	16741	8,748	319:38	1,274	6.87	41.8%
44	16742	4,939	203:02	681	7.25	49.5%
45	16743	8,956	313:32	1,172	7.64	40.3%
46	16744	9,392	254:18	1,391	6.75	30.1%
47	16745	5,211	153:14	660	7.89	33.2%
48	16746	10,115	564:13	1,683	6.01	64.6%
49	16747	10,889	306:49	1,479	7.36	31.0%
50	16748	9,021	488:38	1,440	6.26	64.5%
51	16749	8,923	502:34	1,415	6.31	65.2%
52	16750	10,748	316:43	1,405	7.65	28.4%
53	16751	9,197	264:30	1,256	7.32	22.8%
54	16752	7,287	223:16	913	7.98	29.4%
Make

 

[Noxqss38242]

Maybe I made it sound complicated? Just need a formula that allows me to alter the "range" of the raw data. So for instance:
In a cell, (let's say M2), I need a formula which range is 16700 - 16750, sum up column B for that range, then divide that by the sum of column D for the same range to get my answer.

Then in another cell,(let's say M3), I need that range (16700 - 16750) and for it to find the average of column F.

Likewise, for all my other cells, I can use that formula and just manually adjust what I want my range to be once I have a working formula.

 

[Noxqss38242]

=sumifs function GOT IT!

Sometimes I stress too fast and post before thinking....