Out of my Element, looking for guidance

[keef2]

Morning all!

My goal is to use a data set from "Master" where user inputs raw data to automatically create another sheet.

Some of the functionality or formatting I need to figure out is in Column C in random rows (no consistency) there is a label of XXXX Jobs where XXXX is the user input year. So all data in the Master is manually grouped by these years.

Question 1: Can anyone suggest how to automatically group these ranges by the first project under the label and then stopping the group at the next label ie. 2020 Jobs to 2021 Jobs and so on...

Question 2: Similarly I am looking to take the Master data to create another sheet where it sorts it by Job # and Year by the user input label. However, i then need to insert 6 or so blank rows below each yearly data sets. Note all the data will be in this sheet that is created from the Master so example would be:

But again would want to insert 6 or so blank rows above the example 2021 JOBS and again at the bottom of the 2021 JOBS.

Here is how the data is listed in the Master:

masterjoblist - V0.1.xlsx
	B	C	D	E	F	G
3237		2021 JOBS
3238	3284	Expeditors Cherry Hill 16	DC	12/7/2020	12/8/2020	$ 5,992
3239	3285	Clarius Park Joliet Bldg 2, Lot 2	JS	12/10/2020		$ 2,556,800
3240	3286	Amazon End State @ Charlie	DC	12/21/2020	1/4/2021	$ 209,902
3241	3287	Hubbell	MS	1/4/2021	1/8/2021	$ 13,970
3242	3288	BioLife Joliet (1-1914)	KA	1/6/2021		$ 24,000
3243	3289	Amazon DCH#6 A/C Install	DC	1/7/2021	1/20/2021	$ 53,200
3244	3290	Amazon DIL7 A/C Install	DC	1/7/2021	1/20/2021	$ -
3245	3291	Enlightened Dispensary	MS	1/8/2021	1/13/2021	$ 5,700
3246	3292	Mazak Expansion	KA	1/11/2021	2/8/2021	$ 105,500
3247	3293	Clarendon Hills Park District CC Renov	MS	1/11/2021		$ 13,160
3248	3294	Chi Pulmonary Sports Med	MS	1/12/2021		$ 1,030
3249	3295	Crossroads Bldg B (Scotts??)	JS	1/12/2021	2/15/2021	$ 3,764,800
3250	3296	Gas N Wash	KA	1/18/2021	3/29/2021	$ 100,900
3251	3297	School Dist 59 Secure Vestibule Renovations	MS	1/27/2021		$ 8,090
3252	3298	Central Properties Truck Center Phase 1	KA	2/2/2021	5/3/2021	$ 260,000
3253	3299	Amazon-DIL3, Academy Renovation	DC	2/25/2021		$ 4,978
3254	3300	2065 George Street Bldg #1 - #3	JS	2/22/2021	6/28/2021	$ 2,387,500
3255	3301	Gold Coast Logistics	KA	3/2/2021	6/7/2021	$ 344,000
3256	3302	Univ of Chgo Landlord Improvements	MS	3/3/2021	3/5/2021	$ 3,620
3257	3303	Dynamic BTS	JS	3/8/2021	6/14/2021	$ 1,556,985
3258	3304	Mokena II Atlas Putty TI	DC	3/8/2021		$ 1,500
3259	3305	Mokena I Spec Office 01 TI	DC	3/10/2021		$ 4,056
3260	3306	Compass Health Center	MS	3/12/2021	3/30/2021	$ 8,390
3261	3307	Life Storage Phase II	DC	3/17/2021	3/25/2021	$ 65,000
3262	3308	Ferrara - Donkey	MS	3/17/2021		$ 64,865
3263	3309	Prologis - 553 S Joliet Rd Make Ready	MS	3/18/2021	4/8/2021	$ 1,650
3264	3310	94 LogisticPark PHIII Bldg 6	JS	3/15/2021	2022	$ 774,100
3265	3311	94 LogisticPark PHIII Bldg 7	JS	3/15/2021	12/1/2021	$ 1,830,750
3266	3312	***VOID***1104 W. 43rd Street	JS	3/17/2021	9/13/2021	$ -
3267	3313	Northpoint - Avenue O	JS	3/26/2021	11/22/2021	$ 1,560,000
3268	3314	Silver Cross MOB	KA	3/26/2021	6/17/2021	$ 233,600
3269	3315	Home Depot Bedford	JS	3/31/2021	7/19/2021	$ 2,669,100
3270	3316	Animal Emergency Clinic	KA	3/31/2021	7/19/2021	$ 25,900
3271	3317	Aurora Self Storage	KA	4/7/2021	8/30/2021	$ 235,000
3272	3318	Connelly Electric Roof Recover	JS	4/6/2021	5/17/2021	$ 150,000
3273	3319	Swedish Hospital	DC	4/9/2021	5/7/2021	$ 20,147
3274	3320	Stream CH1 - Phase III	DC	4/14/2021	4/21/2021	$ 41,450
3275	3321	361 S Forntage Rd TI, Ste 126,128,129,131	MS	4/14/2021		$ 6,130
3276	3322	Palatine Library	DC	4/19/2021	4/26/2021	$ 1,200
3277	3323	Bellwood Spec Bldg	JS	4/9/2021	9/22/2021	$ 789,441
3278	3324	Wehrli Custom Fabrication	JS	4/13/2021	8/23/2021	$ 425,000
3279	3325	4300 Brandon Road	JS	4/21/2021	8/23/2021	$ 2,942,000
3280	3326	2424 S. Halsted Street (Prologis DXH1)	JS	4/21/2021	8/2/2021	$ 977,000
3281	3327	Goya Foods Expansion	JS	4/21/2021	9/1/2021	$ 505,900
3282	3328	Ortho Illinois	KA	4/21/2021	9/6/2021	$ 489,500
3283	3329	Kingsley School Server Relocation 2021	DC	4/28/2021	5/10/2021	$ 3,335
3284	3330	TLC Ingredients Warehouse	JS	4/29/2021	7/12/2021	$ 209,500
3285	3331	AMZ IXD (RFD2) - Project Pumpkin	JS	4/30/2021	8/26/2021	$ 2,218,600
3286	3332	Syngenta	KA	5/3/2021	10/19/2021	$ 443,000
3287	3333	3301 S Brandon Road	JS	5/7/2021	10/11/2021	$ 2,977,395
3288	3334	VWR International Roof Re-Cover	JS	5/4/2021	6/1/2021	$ 368,900
3289	3335	**cancel**Steiner Electric Warehouse Addition	KA	5/10/2021	9/1/2021	$ -
3290	3336	Geneva Middle School	MS	5/14/2021	6/1/2021	$ 20,380
3291	3337	Amazon - MKE6	DC	5/20/2021	6/16/2021	$ 266,180
3292	3338	Ferrara - Mapes Canopies	KA	5/20/2021	7/26/2021	$ 19,200
3293	3339	Amazon DLN5	JS	5/21/2021	11/15/2021	$ 1,031,650
3294	3340	Army Trail Trade Center Bldg #1 and #2	JS	5/28/2021	11/22/2021	$ 888,800
3295	3341	Rosebud Restaurant Addition	KA	5/25/2021	8/30/2021	$ 29,000
3296	3342	Holy Apostles Church	KA	5/24/2021	6/28/2021	$ 15,900
3297	3343	DMG Bloomingdale MRI Power	DC	6/30/2021	7/7/2021	$ 2,350
3298	3344	AMG Dreyer Naperville	MS	7/2/2021	7/12/2021	$ 44,820
3299	3345	Hardinge Group TI	DC	7/7/2021	7/21/2021	$ 20,050
3300	3346	Discount Tire TI	DC	7/7/2021	7/21/2021	$ 12,300
3301	3347	MVCC - Bldg T	DC	7/13/2021	7/26/2021	$ 6,125
3302	3348	North Aurora Golf Course Bldg A	JS	7/9/2021	11/23/2021	$ 351,800
3303	3349	North Aurora Golf Course Bldg B	JS	7/9/2021	11/23/2021	$ 1,725,000
3304	3350	ELP VI	JS	7/15/2021	3/4/2022	$ 3,611,500
3305	3351	Dermody - Elmhurst Logistics	JS	7/26/2021	1/24/2022	$ 905,900
3306	3351.10	Dermody - Elmhurst TI	MS	5/25/2022	6/1/2022	$ 12,130
3307	3352	Cold Summit Development	KA	7/30/2021	3/18/2022	$ 1,893,800
3308	3353	**cancel**CDE Collision Centers**shop drawings only	KA	8/3/2021	9/6/2021	$ 3,500
3309	3354	Grifols PlasmaCare Chicago	MS	8/9/2021	8/23/2021	$ 3,890
3310	3355	PRG Suncast Phase II	JS	8/11/2021	6/1/2022	$ 3,024,000
3311	3356	Winston Knolls School	DC	8/11/2021		$ 15,300
3312	3357	***VOID***Amazon MDWy Geneva (was OXD)	JS	8/16/2021	2/1/2022	$ -
3313	3358	AMZ XLFC (ORD6) Project Pie	JS	8/24/2021	1/6/2022	$ 8,490,000
3314	3359	NSK Canopy	MS	8/24/2021	9/1/2021	$ 18,330
3315	3360	Amazon DXH#8	JS	8/15/2021	######	$ -
3316	3361	Project Sweetness - Landlord Upgrades	MS	8/27/2021	9/8/2021	$ 13,360
3317	3362	The Shade Store, Chicago	KA	8/31/2021	11/1/2021	$ 39,000
3318	3363	Loeber Motors Porsche ACM Repair	KA	9/2/2021	10/1/2021	$ 24,950
3319	3364	1900 Maywood - Spec	JS	9/2/2021	4/1/2022	$ 421,900
3320	3365	Amazon KRB6	DC	9/9/2021	9/1/2021	$ 64,750
3321	3366	***VOID***Carvana Truck & Maintenance Bldg	KA	8/17/2021	4/11/2022	$ -
3322	3367	***VOID***West Chicago 400K	JS	9/14/2021	4/18/2021	$ -
3323	3368	Martin Produce Freezer	MS	9/23/2021	9/25/2021	$ 14,070
3324	3369	Hines VA - EHRM Training	KA	9/27/2021	10/11/2021	$ 9,800
3325	3370	Crossroads 55 Amazon ORD2	DC	10/6/2021	10/15/2021	$ 88,509
3326	3371	LPC Palatine	JS	10/7/2021	10/10/2022	$ 1,624,800
3327	3372	LPC Oak Forest Spec	JS	10/7/2021	11/24/2022	$ 3,459,500
3328	3373	LPC Oak Forest Bldg #2	JS	10/7/2021	12/25/2022	$ -
3329	3374	LPC Aurora OD	JS	10/7/2021	11/4/2022	$ 1,269,600
3330	3375	LPC Aurora 3000 W Diehl	JS	10/7/2021	9/24/2022	$ 1,852,650
3331	3376	Project River	JS	10/4/2021	8/22/2022	$ 4,435,700
3332	3377	ORD Fuel Company LLC Building	KA	10/19/2021	2/14/2022	$ 208,000
3333	3378	EdgeConnex - CHI02	DC	10/21/2021	11/15/2021	$ 137,500
3334	3379	Mokena I FedEx TI	DC	10/21/2021		$ 27,150
3335	3380	Central Park Properties Canopies	KA	10/28/2021		$ 96,000
3336	3381	Fabricators and Maufacturers Association	KA	11/2/2021	11/8/2021	$ 5,454
3337	3382	PRG Laraway Spec Bldg	JS	11/2/2021	8/1/2022	$ 3,100,640
3338	3383	Tinley Park Business Ctr Bldg #1	JS	11/2/2021	######	$ -
3339	3384	The Slovenian Catholic Center	JS	11/5/2021		$ -
3340	3385	DD Discounts	KA	11/9/2021	1/4/2022	$ 28,200
3341	3386	BC Terminals	KA	11/8/2021	2/1/2022	$ 37,000
3342	3387	Gas N Wash C Store	KA	11/8/2021	8/18/2022	$ 336,100
3343	3388	Schaumburg Honda	KA	11/10/2021	2/14/2022	$ 802,500
3344	3389	Kanebridge Expansion	KA	11/16/2021	4/21/2022	$ 296,495
3345		2022 JOBS
3346	3390	Beyond Fresh	DC	12/6/2021	12/10/2021	$ 92,575
3347	3391	Cary Grove Park Aquatic Facility	KA	12/9/2021	3/1/2022	$ 75,950
3348	3392	Digital Realty Repairs	KA	12/10/2021	7/5/2022	$ 215,000
3349	3393	Madison Meadow ACM Panel Repair	KA	1/12/2022	2/1/2022	$ 18,218
3350	3394	Molto, Minooka Ridge Spec WH	JS	1/13/2022	######	$ -
3351	3395	Lion Electric, Lots 2 & 3 Clarius Park	DC	1/20/2022	1/27/2022	$ 71,200
3352	3396	Target - Exchange 55	DC	2/8/2022	2/8/2022	$ 1,250
3353	3397	DR1V - Phase 3 Metal Wall Panels	KA	2/9/2022	5/2/2022	$ 25,500
3354	3398	DR1V - Phase 3 Membrane Roofing	DC	2/9/2022		$ 15,260
3355	3399	Pure's Food Specialties Inc	MS	2/15/2022	2/18/2022	$ 21,090
3356	3400	Centerpoint Schweitzer Road Spec	JS	1/19/2022	8/23/2022	$ 5,321,125
3357	3401	Centerpoint Amazon TI	DC	2/16/2022	2/24/2022	$ 176,600
3358	3402	DS Containers TI	MS	2/16/2022	2/17/2022	$ 850
3359	3403	Ridgeline-NW Gateway Ctr Arlington Heights Spec Bldg #1&#2	JS	1/19/2022	9/1/2022	$ 2,718,220
3360	3404	3351 Brandon Road	JS	2/17/2022	6/15/2022	$ 5,161,200
3361	3405	94 Logistics LPC Bldg #3	JS	######	1/16/2023
3362	3406	94 Logistics LPC Bldg #4	JS	######	######
3363	3407	94 Logistics LPC Bldg #5	JS	######	3/1/2023
3364	3408	Amazon IGQ2 Walkway Pads	DC	3/8/2022	3/30/2022	$ 87,950
3365	3409	Duly Plainfield Infusion/PT	MS	3/10/2022	3/28/2022	$ 15,860
3366	3410	Ortho Illinois ACM/Screenwall	KA	3/7/2022	9/15/2022	$ 75,000
3367	3411	Dhamer Drive Spec Bldg	JS	3/22/2022	1/23/2023	$ 3,576,900
3368	3412	LaRabida Children's Hospital (Roof Cut & Patch)	MS	3/24/2022	4/12/2022	$ 2,140
3369	3413	Orchard Road Spec TI	DC	3/29/2022	5/16/2022	$ 7,600
3370	3414	Mokena I & II Trash Enclosures	KA	4/5/2022		$ 8,000
3371	3415	Scannell Tinley Park Bldg #2	JS	3/31/2022	5/8/2023	$ -
3372	3416	Bridgepoint McCook Bldg #1	JS	4/5/2022	5/15/2023	$ 4,725,800
3373	3417	Bridgepoint McCook Bldg #2	JS	4/5/2022	7/3/2023	$ 997,300
3374	3418	Geodis/Samsung TI	DC	4/8/2022	4/25/2022	$ 20,275
3375	3419	Planet Fitness - Rockford	MS	4/11/2022	4/18/2022	$ 5,000
3376	3420	Rockford LL Work - Tenant A	MS	4/11/2022	4/18/2022	$ 9,580
3377	3421	AMG Des Plaines Retail Imaging	KA	4/11/2022	6/1/2022	$ 165,300
3378	3422	Montgomery Terminal Bldg A&B	KA	4/14/2022	7/1/2023	$ 1,224,200
3379	3423	725 South Wells	DC	4/15/2022	5/16/2022	$ 18,350
3380	3424	ELP 55 Bldg VII	JS	4/14/2022	4/10/2023	$ 5,811,100
3381	3425	Fabricators and Maufacturers 2022 Snow Guard	KA	5/3/2022	6/1/2022	$ 8,950
3382	3426	Crystal Lake Façade Phase 2	KA/DC	5/9/2022	7/11/2022	$ 85,000
3383	3427	Edwards-Elmhurst Health-Elmhurst Schiller Clinic	DC	5/17/2022	5/25/2022	$ 3,475
3384	3428	Nordeen Dolinger	JS	5/19/2022	7/17/2023	$ 5,866,000
3385	3429	Project Overlook	JS	5/19/2022	7/18/2023	$ 1,836,500
3386	3430	Clyde's Delicious Donuts	DC	6/6/2022	7/6/2022	$ 130,000
3387	3431	Zion Benton HS Science Wing Renovation	MS	6/17/2022	9/15/2022	$ 96,745
3388	3432	Mistica Foods	MS	6/28/2022	7/7/2022	$ 93,230
3389	3433	SC Johnson Bldg #2	DC	7/8/2022	7/18/2022	$ 30,800
3390	3434	Maybach International Inc	KA	7/27/2022	2/8/2023	$ 470,000
3391	3435	Roesch Volkswagen	KA	8/2/2022	11/17/2022	$ 97,500
MASTER

Cell Formulas
Range	Formula
G3251	G3251	=2160+5930
G3253	G3253	=3978+1000
B3237:B3305,B3307:B3391	B3237	=IF(COUNTIF(C3237,"*JOBS*"),"",IF(C3237="","",IF(B3236="",INT(B3235)+1,IF(C3237<>C3236,INT(B3236)+1,INT(B3236)+(COUNTIF($C$1:C3237,$C$2)-1)/10))))
G3313	G3313	=4931000+3511000+48000
G3342	G3342	=174100+129900+32100
G3387	G3387	=85745+11000

Named Ranges
Name	Refers To	Cells
Print_Area	=MASTER!$B$3345:$G$3351	B3346:B3347

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
3237:6000	Expression	=COUNTIF($C3237,"*JOBS*")	text	NO
B3238:B6000	Expression	=NOT(ISFORMULA(B3238))	text	NO

 

[keef2]

Here is a very crude example of more of what i am looking for without the user having to manually adjust/format.

masterjoblist - V0.1.xlsx
	B	C	D	E	F	G
989		2021 JOBS
990	3284	Expeditors Cherry Hill 16	DC	12/7/2020	12/8/2020	$ 5,992.00
991	3285	Clarius Park Joliet Bldg 2, Lot 2	JS	########	1/0/1900	$ 2,556,800.00
992	3286	Amazon End State @ Charlie	DC	########	1/4/2021	$ 209,902.00
993	3287	Hubbell	MS	1/4/2021	1/8/2021	$ 13,970.00
994	3288	BioLife Joliet (1-1914)	KA	1/6/2021	1/0/1900	$ 24,000.00
995	3289	Amazon DCH#6 A/C Install	DC	1/7/2021	1/20/2021	$ 53,200.00
996	3290	Amazon DIL7 A/C Install	DC	1/7/2021	1/20/2021	$ -
997	3291	Enlightened Dispensary	MS	1/8/2021	1/13/2021	$ 5,700.00
998	3292	Mazak Expansion	KA	1/11/2021	2/8/2021	$ 105,500.00
999	3293	Clarendon Hills Park District CC Renov	MS	1/11/2021	1/0/1900	$ 13,160.00
1000	3294	Chi Pulmonary Sports Med	MS	1/12/2021	1/0/1900	$ 1,030.00
1001	3295	Crossroads Bldg B (Scotts??)	JS	1/12/2021	2/15/2021	$ 3,764,800.00
1002	3296	Gas N Wash	KA	1/18/2021	3/29/2021	$ 100,900.00
1003	3297	School Dist 59 Secure Vestibule Renovations	MS	1/27/2021	1/0/1900	$ 8,090.00
1004	3298	Central Properties Truck Center Phase 1	KA	2/2/2021	5/3/2021	$ 260,000.00
1005	3299	Amazon-DIL3, Academy Renovation	DC	2/25/2021	1/0/1900	$ 4,978.00
1006	3300	2065 George Street Bldg #1 - #3	JS	2/22/2021	6/28/2021	$ 2,387,500.00
1007	3301	Gold Coast Logistics	KA	3/2/2021	6/7/2021	$ 344,000.00
1008	3302	Univ of Chgo Landlord Improvements	MS	3/3/2021	3/5/2021	$ 3,620.00
1009	3303	Dynamic BTS	JS	3/8/2021	6/14/2021	$ 1,556,985.00
1010	3304	Mokena II Atlas Putty TI	DC	3/8/2021	1/0/1900	$ 1,500.00
1011	3305	Mokena I Spec Office 01 TI	DC	3/10/2021	1/0/1900	$ 4,056.00
1012	3306	Compass Health Center	MS	3/12/2021	3/30/2021	$ 8,390.00
1013	3307	Life Storage Phase II	DC	3/17/2021	3/25/2021	$ 65,000.00
1014	3308	Ferrara - Donkey	MS	3/17/2021	1/0/1900	$ 64,865.00
1015	3309	Prologis - 553 S Joliet Rd Make Ready	MS	3/18/2021	4/8/2021	$ 1,650.00
1016	3310	94 LogisticPark PHIII Bldg 6	JS	3/15/2021	2022	$ 774,100.00
1017	3311	94 LogisticPark PHIII Bldg 7	JS	3/15/2021	12/1/2021	$ 1,830,750.00
1018	3312	***VOID***1104 W. 43rd Street	JS	3/17/2021	9/13/2021	$ -
1019	3313	Northpoint - Avenue O	JS	3/26/2021	########	$ 1,560,000.00
1020	3314	Silver Cross MOB	KA	3/26/2021	6/17/2021	$ 233,600.00
1021	3315	Home Depot Bedford	JS	3/31/2021	7/19/2021	$ 2,669,100.00
1022	3316	Animal Emergency Clinic	KA	3/31/2021	7/19/2021	$ 25,900.00
1023	3317	Aurora Self Storage	KA	4/7/2021	8/30/2021	$ 235,000.00
1024	3318	Connelly Electric Roof Recover	JS	4/6/2021	5/17/2021	$ 150,000.00
1025	3319	Swedish Hospital	DC	4/9/2021	5/7/2021	$ 20,147.00
1026	3320	Stream CH1 - Phase III	DC	4/14/2021	4/21/2021	$ 41,450.00
1027	3321	361 S Forntage Rd TI, Ste 126,128,129,131	MS	4/14/2021	1/0/1900	$ 6,130.00
1028	3322	Palatine Library	DC	4/19/2021	4/26/2021	$ 1,200.00
1029	3323	Bellwood Spec Bldg	JS	4/9/2021	9/22/2021	$ 789,441.00
1030	3324	Wehrli Custom Fabrication	JS	4/13/2021	8/23/2021	$ 425,000.00
1031	3325	4300 Brandon Road	JS	4/21/2021	8/23/2021	$ 2,942,000.00
1032	3326	2424 S. Halsted Street (Prologis DXH1)	JS	4/21/2021	8/2/2021	$ 977,000.00
1033	3327	Goya Foods Expansion	JS	4/21/2021	9/1/2021	$ 505,900.00
1034	3328	Ortho Illinois	KA	4/21/2021	9/6/2021	$ 489,500.00
1035	3329	Kingsley School Server Relocation 2021	DC	4/28/2021	5/10/2021	$ 3,335.00
1036	3330	TLC Ingredients Warehouse	JS	4/29/2021	7/12/2021	$ 209,500.00
1037	3331	AMZ IXD (RFD2) - Project Pumpkin	JS	4/30/2021	8/26/2021	$ 2,218,600.00
1038	3332	Syngenta	KA	5/3/2021	########	$ 443,000.00
1039	3333	3301 S Brandon Road	JS	5/7/2021	########	$ 2,977,395.00
1040	3334	VWR International Roof Re-Cover	JS	5/4/2021	6/1/2021	$ 368,900.00
1041	3335	**cancel**Steiner Electric Warehouse Addition	KA	5/10/2021	9/1/2021	$ -
1042	3336	Geneva Middle School	MS	5/14/2021	6/1/2021	$ 20,380.00
1043	3337	Amazon - MKE6	DC	5/20/2021	6/16/2021	$ 266,180.00
1044	3338	Ferrara - Mapes Canopies	KA	5/20/2021	7/26/2021	$ 19,200.00
1045	3339	Amazon DLN5	JS	5/21/2021	########	$ 1,031,650.00
1046	3340	Army Trail Trade Center Bldg #1 and #2	JS	5/28/2021	########	$ 888,800.00
1047	3341	Rosebud Restaurant Addition	KA	5/25/2021	8/30/2021	$ 29,000.00
1048	3342	Holy Apostles Church	KA	5/24/2021	6/28/2021	$ 15,900.00
1049	3343	DMG Bloomingdale MRI Power	DC	6/30/2021	7/7/2021	$ 2,350.00
1050	3344	AMG Dreyer Naperville	MS	7/2/2021	7/12/2021	$ 44,820.00
1051	3345	Hardinge Group TI	DC	7/7/2021	7/21/2021	$ 20,050.00
1052	3346	Discount Tire TI	DC	7/7/2021	7/21/2021	$ 12,300.00
1053	3347	MVCC - Bldg T	DC	7/13/2021	7/26/2021	$ 6,125.00
1054	3348	North Aurora Golf Course Bldg A	JS	7/9/2021	########	$ 351,800.00
1055	3349	North Aurora Golf Course Bldg B	JS	7/9/2021	########	$ 1,725,000.00
1056	3350	ELP VI	JS	7/15/2021	3/4/2022	$ 3,611,500.00
1057	3351	Dermody - Elmhurst Logistics	JS	7/26/2021	1/24/2022	$ 905,900.00
1058	3351.10	Dermody - Elmhurst TI	MS	5/25/2022	6/1/2022	$ 12,130.00
1059	3352	Cold Summit Development	KA	7/30/2021	3/18/2022	$ 1,893,800.00
1060	3353	**cancel**CDE Collision Centers**shop drawings only	KA	8/3/2021	9/6/2021	$ 3,500.00
1061	3354	Grifols PlasmaCare Chicago	MS	8/9/2021	8/23/2021	$ 3,890.00
1062	3355	PRG Suncast Phase II	JS	8/11/2021	6/1/2022	$ 3,024,000.00
1063	3356	Winston Knolls School	DC	8/11/2021	1/0/1900	$ 15,300.00
1064	3357	***VOID***Amazon MDWy Geneva (was OXD)	JS	8/16/2021	2/1/2022	$ -
1065	3358	AMZ XLFC (ORD6) Project Pie	JS	8/24/2021	1/6/2022	$ 8,490,000.00
1066	3359	NSK Canopy	MS	8/24/2021	9/1/2021	$ 18,330.00
1067	3360	Amazon DXH#8	JS	8/15/2021	3/31/2023	$ -
1068	3361	Project Sweetness - Landlord Upgrades	MS	8/27/2021	9/8/2021	$ 13,360.00
1069	3362	The Shade Store, Chicago	KA	8/31/2021	11/1/2021	$ 39,000.00
1070	3363	Loeber Motors Porsche ACM Repair	KA	9/2/2021	10/1/2021	$ 24,950.00
1071	3364	1900 Maywood - Spec	JS	9/2/2021	4/1/2022	$ 421,900.00
1072	3365	Amazon KRB6	DC	9/9/2021	9/1/2021	$ 64,750.00
1073	3366	***VOID***Carvana Truck & Maintenance Bldg	KA	8/17/2021	4/11/2022	$ -
1074	3367	***VOID***West Chicago 400K	JS	9/14/2021	4/18/2021	$ -
1075	3368	Martin Produce Freezer	MS	9/23/2021	9/25/2021	$ 14,070.00
1076	3369	Hines VA - EHRM Training	KA	9/27/2021	########	$ 9,800.00
1077	3370	Crossroads 55 Amazon ORD2	DC	10/6/2021	########	$ 88,509.00
1078	3371	LPC Palatine	JS	10/7/2021	########	$ 1,624,800.00
1079	3372	LPC Oak Forest Spec	JS	10/7/2021	########	$ 3,459,500.00
1080	3373	LPC Oak Forest Bldg #2	JS	10/7/2021	########	$ -
1081	3374	LPC Aurora OD	JS	10/7/2021	11/4/2022	$ 1,269,600.00
1082	3375	LPC Aurora 3000 W Diehl	JS	10/7/2021	9/24/2022	$ 1,852,650.00
1083	3376	Project River	JS	10/4/2021	8/22/2022	$ 4,435,700.00
1084	3377	ORD Fuel Company LLC Building	KA	########	2/14/2022	$ 208,000.00
1085	3378	EdgeConnex - CHI02	DC	########	########	$ 137,500.00
1086	3379	Mokena I FedEx TI	DC	########	1/0/1900	$ 27,150.00
1087	3380	Central Park Properties Canopies	KA	########	1/0/1900	$ 96,000.00
1088	3381	Fabricators and Maufacturers Association	KA	11/2/2021	11/8/2021	$ 5,454.00
1089	3382	PRG Laraway Spec Bldg	JS	11/2/2021	8/1/2022	$ 3,100,640.00
1090	3383	Tinley Park Business Ctr Bldg #1	JS	11/2/2021	########	$ -
1091	3384	The Slovenian Catholic Center	JS	11/5/2021	1/0/1900	$ -
1092	3385	DD Discounts	KA	11/9/2021	1/4/2022	$ 28,200.00
1093	3386	BC Terminals	KA	11/8/2021	2/1/2022	$ 37,000.00
1094	3387	Gas N Wash C Store	KA	11/8/2021	8/18/2022	$ 336,100.00
1095	3388	Schaumburg Honda	KA	########	2/14/2022	$ 802,500.00
1096	3389	Kanebridge Expansion	KA	########	4/21/2022	$ 296,495.00
1097				SOLD 2021		$72,268,069.00
1098
1099
1100						Prev SOLD 2020
1101						Diff from 2020
1102
1103		2022 JOBS
1104	3390	Beyond Fresh	DC	12/6/2021	########	$ 92,575.00
1105	3391	Cary Grove Park Aquatic Facility	KA	12/9/2021	3/1/2022	$ 75,950.00
1106	3392	Digital Realty Repairs	KA	########	7/5/2022	$ 215,000.00
1107	3393	Madison Meadow ACM Panel Repair	KA	1/12/2022	2/1/2022	$ 18,218.00
1108	3394	Molto, Minooka Ridge Spec WH	JS	1/13/2022	########	$ -
1109	3395	Lion Electric, Lots 2 & 3 Clarius Park	DC	1/20/2022	1/27/2022	$ 71,200.00
1110	3396	Target - Exchange 55	DC	2/8/2022	2/8/2022	$ 1,250.00
1111	3397	DR1V - Phase 3 Metal Wall Panels	KA	2/9/2022	5/2/2022	$ 25,500.00
1112	3398	DR1V - Phase 3 Membrane Roofing	DC	2/9/2022	1/0/1900	$ 15,260.00
1113	3399	Pure's Food Specialties Inc	MS	2/15/2022	2/18/2022	$ 21,090.00
1114	3400	Centerpoint Schweitzer Road Spec	JS	1/19/2022	8/23/2022	$ 5,321,125.00
1115	3401	Centerpoint Amazon TI	DC	2/16/2022	2/24/2022	$ 176,600.00
1116	3402	DS Containers TI	MS	2/16/2022	2/17/2022	$ 850.00
1117	3403	Ridgeline-NW Gateway Ctr Arlington Heights Spec Bldg #1&#2	JS	1/19/2022	9/1/2022	$ 2,718,220.00
1118	3404	3351 Brandon Road	JS	2/17/2022	6/15/2022	$ 5,161,200.00
1119	3405	94 Logistics LPC Bldg #3	JS	2/24/2022	1/16/2023	$ -
1120	3406	94 Logistics LPC Bldg #4	JS	2/24/2022	3/15/2023	$ -
1121	3407	94 Logistics LPC Bldg #5	JS	2/24/2022	3/1/2023	$ -
1122	3408	Amazon IGQ2 Walkway Pads	DC	3/8/2022	3/30/2022	$ 87,950.00
1123	3409	Duly Plainfield Infusion/PT	MS	3/10/2022	3/28/2022	$ 15,860.00
1124	3410	Ortho Illinois ACM/Screenwall	KA	3/7/2022	9/15/2022	$ 75,000.00
1125	3411	Dhamer Drive Spec Bldg	JS	3/22/2022	1/23/2023	$ 3,576,900.00
1126	3412	LaRabida Children's Hospital (Roof Cut & Patch)	MS	3/24/2022	4/12/2022	$ 2,140.00
1127	3413	Orchard Road Spec TI	DC	3/29/2022	5/16/2022	$ 7,600.00
1128	3414	Mokena I & II Trash Enclosures	KA	4/5/2022	1/0/1900	$ 8,000.00
1129	3415	Scannell Tinley Park Bldg #2	JS	3/31/2022	5/8/2023	$ -
1130	3416	Bridgepoint McCook Bldg #1	JS	4/5/2022	5/15/2023	$ 4,725,800.00
1131	3417	Bridgepoint McCook Bldg #2	JS	4/5/2022	7/3/2023	$ 997,300.00
1132	3418	Geodis/Samsung TI	DC	4/8/2022	4/25/2022	$ 20,275.00
1133	3419	Planet Fitness - Rockford	MS	4/11/2022	4/18/2022	$ 5,000.00
1134	3420	Rockford LL Work - Tenant A	MS	4/11/2022	4/18/2022	$ 9,580.00
1135	3421	AMG Des Plaines Retail Imaging	KA	4/11/2022	6/1/2022	$ 165,300.00
1136	3422	Montgomery Terminal Bldg A&B	KA	4/14/2022	7/1/2023	$ 1,224,200.00
1137	3423	725 South Wells	DC	4/15/2022	5/16/2022	$ 18,350.00
1138	3424	ELP 55 Bldg VII	JS	4/14/2022	4/10/2023	$ 5,811,100.00
1139	3425	Fabricators and Maufacturers 2022 Snow Guard	KA	5/3/2022	6/1/2022	$ 8,950.00
1140	3426	Crystal Lake Façade Phase 2	KA/DC	5/9/2022	7/11/2022	$ 85,000.00
1141	3427	Edwards-Elmhurst Health-Elmhurst Schiller Clinic	DC	5/17/2022	5/25/2022	$ 3,475.00
1142	3428	Nordeen Dolinger	JS	5/19/2022	7/17/2023	$ 5,866,000.00
1143	3429	Project Overlook	JS	5/19/2022	7/18/2023	$ 1,836,500.00
1144	3430	Clyde's Delicious Donuts	DC	6/6/2022	7/6/2022	$ 130,000.00
1145	3431	Zion Benton HS Science Wing Renovation	MS	6/17/2022	9/15/2022	$ 96,745.00
1146	3432	Mistica Foods	MS	6/28/2022	7/7/2022	$ 93,230.00
1147	3433	SC Johnson Bldg #2	DC	7/8/2022	7/18/2022	$ 30,800.00
1148	3434	Maybach International Inc	KA	7/27/2022	2/8/2023	$ 470,000.00
1149	3435	Roesch Volkswagen	KA	8/2/2022	########	$ 97,500.00
PM BREAKDOWN

Cell Formulas
Range	Formula
B990:G1096	B990	=+MASTER!B3238
G1097	G1097	=SUM(G990:G1096)
B1104:G1149	B1104	=+MASTER!B3346

 

[keef2]

Last question, lastly I have tried sum product, sumifs etc for the last equation i am trying to do but no success. Looking to also be able to do the following:
Sum column G by Month and by year. Any help would again be appreciated.

Here is the table that i will use to fill in with the master data per the sumif = month and year. Right now this is manual and just want to streamline things. Please let me know if you have any questiosn as all these request for help will be a huge help!

masterjoblist - V0.1.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Monthly Sales
2
3		January	Feb	March	April	May	June	July	Aug	Sept	Oct	Nov	Dec	TOTAL SALES
4	2020	$ 1,057,457	$ 5,948,478	$ 2,712,030	$ 1,014,750	$ 1,511,408	$ 1,182,030	$ 4,516,640	$ 3,857,360	$ 2,769,944	$ 1,685,050	$ 1,704,698	$ 2,562,792	$ 30,522,637	2020
5	2019	$ 113,838	$ 7,071,370	$ 1,380,520	$ 747,983	$ 5,226,733	$ 1,606,520	$ 7,148,845	$ 1,351,090	$ 792,577	$ 7,278,820	$ 4,818,686	$ 2,516,597	$ 40,053,579	2019
6	2018	$ 4,351,558	$ 270,195	$ 2,116,150	$ 5,489,089	$ 2,985,495	$ 2,442,750	$ 8,344,972	$ 2,442,865	$ 3,261,885	$ 367,150	$ 304,861	$ 3,049,779	$ 35,426,749	2018
7	2017	$ 3,465,280	$ 1,513,817	$ 6,584,535	$ 879,800	$ 1,217,650	$ 2,193,147	$ 298,450	$ 3,302,787	$ 2,806,055	$ 3,908,009	$ 458,937	$ 1,412,888	$ 28,043,372	2017
8	2016	$ 409,465	$ 1,832,825	$ 3,244,407	$ 3,861,764	$ 2,306,938	$ 5,472,024	$ 843,556	$ 8,298,244	$ 4,031,216	$ 1,192,207	$ 7,591,000	$ 92,750	$ 39,178,412	2016
9	2015	$ 2,533,585	$ 2,091,747	$ 4,494,199	$ 1,702,275	$ 1,005,674	$ 2,167,017	$ 7,680,620	$ 110,592	$ 4,010,990	$ 10,878,519	$ 95,300	$ 3,564,216	$ 40,336,749	2015
10	2014	$ 2,078,895	$ 2,626,000	$ 6,654,385	$ 36,380	$ 2,859,745	$ 831,390	$ 1,937,547	$ 3,483,615	$ 2,140,838	$ 647,872	$ 1,137,422	$ 67,600	$ 24,503,703	2014
11	2013	$ 3,093,415	$ 1,090,780	$ 2,249,641	$ 1,796,440	$ 1,717,774	$ 1,044,612	$ 1,825,158	$ 3,733,815	$ 637,388	$ 869,792	$ 2,370,985	$ 823,075	$ 21,252,875	2013
12	2012	$ 217,787	$ 714,700	$ 80,019	$ 7,908,552	$ 3,674,515	$ 522,802	$ 2,398,672	$ 1,251,678	$ 28,843	$ 17,573	$ 9,400	$ 847,565	$ 17,672,106	2012
sales by month

Cell Formulas
Range	Formula
C4	C4	=+SUM(+MASTER!G3157:G3162)
D4	D4	=+SUM(+MASTER!G3163:G3171)
E4	E4	=+SUM(+MASTER!G3172:G3181)
F4	F4	=+SUM(+MASTER!G3182:G3188)
G4	G4	=+SUM(+MASTER!G3189:G3192)
H4	H4	=+SUM(+MASTER!G3193:G3201)
I4	I4	=+SUM(+MASTER!G3202:G3207)
J4	J4	=+SUM(+MASTER!G3208:G3215)
K4	K4	=+SUM(+MASTER!G3216:G3225)
L4	L4	=+SUM(+MASTER!G3226:G3234)
M4	M4	=+SUM(+MASTER!G3235:G3236)
N4	N4	=+SUM(+MASTER!G3238:G3239)
O4:O6,O11:O12	O4	=SUM(C4:N4)
P4:P12	P4	=+B4
C5	C5	=+SUM(+MASTER!G3054:G3062)
D5	D5	=+SUM(+MASTER!G3063:G3071)
E5	E5	=+SUM(+MASTER!G3072:G3086)
F5	F5	=+SUM(+MASTER!G3087:G3094)
G5	G5	=+SUM(+MASTER!G3095:G3105)+MASTER!G3109
H5	H5	=+SUM(+MASTER!G3106:G3108)+MASTER!G3110
I5	I5	=+SUM(+MASTER!G3112:G3123)
J5	J5	=+SUM(+MASTER!G3124:G3129)
K5	K5	=+SUM(+MASTER!G3130:G3133)
L5	L5	=+SUM(+MASTER!G3134:G3140)
M5	M5	=+SUM(+MASTER!G3141:G3148)
N5	N5	=+SUM(+MASTER!G3150:G3156)
C6	C6	=+SUM(MASTER!G2945:G2952)+MASTER!G2962
D6	D6	=+SUM(MASTER!G2953:G2958)+MASTER!G2961
E6	E6	=+SUM(MASTER!G2959:G2960)+SUM(MASTER!G2963:MASTER!G2970)+MASTER!G2973
F6	F6	=+SUM(MASTER!G2971:G2972)+SUM(MASTER!G2974:MASTER!G2984)
G6	G6	=+SUM(MASTER!G2985:G2994)
H6	H6	=+SUM(MASTER!G2995:G3005)
I6	I6	=+SUM(MASTER!G3006:G3015)
J6	J6	=+SUM(MASTER!G3016:G3021)
K6	K6	=+SUM(MASTER!G3022:G3030)
L6	L6	=+SUM(MASTER!G3031:G3035)
M6	M6	=+SUM(MASTER!G3036:G3041)
N6	N6	=+SUM(MASTER!G3043:G3053)
C7	C7	=+MASTER!G2836+MASTER!G2839
D7	D7	=+MASTER!G2837+MASTER!G2838+SUM(MASTER!G2840:G2844)
E7	E7	=+SUM(MASTER!G2845:G2856)
F7	F7	=+SUM(MASTER!G2857:G2861)
G7	G7	=+SUM(MASTER!G2862:G2870)
H7	H7	=+SUM(MASTER!G2871:G2884)
I7	I7	=+SUM(MASTER!G2885:G2891)
J7	J7	=+SUM(MASTER!G2892:G2904)
K7	K7	=+SUM(MASTER!G2905:G2915)
L7	L7	=+SUM(MASTER!G2916:G2930)
M7	M7	=+SUM(MASTER!G2931:G2937)
N7	N7	=+SUM(MASTER!G2939:G2944)
O7:O10	O7	=SUM(B7:N7)
C8	C8	=SUM(MASTER!G2753:G2760)
D8	D8	=SUM(MASTER!G2761:G2766)
E8	E8	=SUM(MASTER!G2767:G2772)
F8	F8	=SUM(MASTER!G2773:G2788)
G8	G8	=SUM(MASTER!H2789:MASTER!G2794)
H8	H8	=SUM(MASTER!G2795:G2803)
I8	I8	=SUM(MASTER!G2804:G2808)
J8	J8	=SUM(MASTER!G2809:G2819)
K8	K8	=SUM(MASTER!G2820:G2826)
L8	L8	=SUM(MASTER!G2827:G2830)-MASTER!G2829
M8	M8	=+MASTER!G2829+MASTER!G2831+MASTER!G2832
N8	N8	=+MASTER!G2834+MASTER!G2835
C9	C9	=SUM(MASTER!G2654:G2661)
D9	D9	=SUM(MASTER!G2662:G2664)
E9	E9	=SUM(MASTER!G2665:G2673)+MASTER!G2693+MASTER!G2682
F9	F9	=SUM(MASTER!G2674:G2681)+(MASTER!G2685+MASTER!G2686)+MASTER!G2707
G9	G9	=SUM(MASTER!G2683:G2684)+MASTER!G2687+MASTER!G2688+MASTER!G2689
H9	H9	=SUM(MASTER!G2690:G2706)-MASTER!G2693
I9	I9	=SUM(MASTER!G2708:G2718)
J9	J9	=SUM(MASTER!G2719:G2724)+MASTER!G2737
K9	K9	=SUM(MASTER!G2725:G2734)
L9	L9	=SUM(MASTER!G2735:G2746)-MASTER!G2737
M9	M9	=SUM(MASTER!G2747)
N9	N9	=SUM(MASTER!G2749:G2752)
C10	C10	=SUM(MASTER!G2570:G2574)+MASTER!G2576
D10	D10	=SUM(MASTER!G2575+MASTER!G2577+MASTER!G2578)
E10	E10	=SUM(MASTER!G2579:G2588)
F10	F10	=SUM(MASTER!G2589)
G10	G10	=SUM(MASTER!G2590:G2599)
H10	H10	=SUM(MASTER!G2600:G2603)
I10	I10	=SUM(MASTER!G2604:G2618)
J10	J10	=SUM(MASTER!G2619:G2628)+MASTER!G2634
K10	K10	=SUM(MASTER!G2629:G2637)-MASTER!G2634
L10	L10	=SUM(MASTER!G2638:G2644)
M10	M10	=SUM(MASTER!G2645:G2649)
N10	N10	=SUM(MASTER!G2651:G2653)
C11	C11	=SUM(MASTER!G2468:G2472)
D11	D11	=SUM(MASTER!G2473:G2478)
E11	E11	=SUM(MASTER!G2479:G2487)
F11	F11	=SUM(MASTER!G2488:G2495)
G11	G11	=SUM(MASTER!G2496:G2509)
H11	H11	=SUM(MASTER!G2510:G2518)
I11	I11	=SUM(MASTER!G2519:G2529)
J11	J11	=SUM(MASTER!G2530:G2544)
K11	K11	=SUM(MASTER!G2545:G2551)
L11	L11	=SUM(MASTER!G2552:G2557)
M11	M11	=SUM(MASTER!G2558:G2566)
N11	N11	=SUM(MASTER!G2568:G2569)
C12	C12	=SUM(MASTER!G2372:G2379)+MASTER!G2369
D12	D12	=SUM(MASTER!G2380:G2384)
E12	E12	=SUM(MASTER!G2385:G2391)
F12	F12	=SUM(MASTER!G2392:G2403)+MASTER!G2406
G12	G12	=SUM(MASTER!G2404:G2405)+SUM(MASTER!G2407:G2417)
H12	H12	=SUM(MASTER!G2418:G2422)+MASTER!G2432
I12	I12	=SUM(MASTER!G2423:G2431)+MASTER!G2433+MASTER!G2434+MASTER!G2435
J12	J12	=SUM(MASTER!G2436:G2445)
K12	K12	=SUM(MASTER!G2447:G2450)
L12	L12	=SUM(MASTER!G2451:G2454)
M12	M12	=SUM(MASTER!G2455:G2457)
N12	N12	=SUM(MASTER!G2459:G2467)

 

[keef2]

Last question, lastly I have tried sum product, sumifs etc for the last equation i am trying to do but no success. Looking to also be able to do the following:
Sum column G by Month and by year. Any help would again be appreciated.

Here is the table that i will use to fill in with the master data per the sumif = month and year. Right now this is manual and just want to streamline things. Please let me know if you have any questiosn as all these request for help will be a huge help!

masterjoblist - V0.1.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Monthly Sales
2
3		January	Feb	March	April	May	June	July	Aug	Sept	Oct	Nov	Dec	TOTAL SALES
4	2020	$ 1,057,457	$ 5,948,478	$ 2,712,030	$ 1,014,750	$ 1,511,408	$ 1,182,030	$ 4,516,640	$ 3,857,360	$ 2,769,944	$ 1,685,050	$ 1,704,698	$ 2,562,792	$ 30,522,637	2020
5	2019	$ 113,838	$ 7,071,370	$ 1,380,520	$ 747,983	$ 5,226,733	$ 1,606,520	$ 7,148,845	$ 1,351,090	$ 792,577	$ 7,278,820	$ 4,818,686	$ 2,516,597	$ 40,053,579	2019
6	2018	$ 4,351,558	$ 270,195	$ 2,116,150	$ 5,489,089	$ 2,985,495	$ 2,442,750	$ 8,344,972	$ 2,442,865	$ 3,261,885	$ 367,150	$ 304,861	$ 3,049,779	$ 35,426,749	2018
7	2017	$ 3,465,280	$ 1,513,817	$ 6,584,535	$ 879,800	$ 1,217,650	$ 2,193,147	$ 298,450	$ 3,302,787	$ 2,806,055	$ 3,908,009	$ 458,937	$ 1,412,888	$ 28,043,372	2017
8	2016	$ 409,465	$ 1,832,825	$ 3,244,407	$ 3,861,764	$ 2,306,938	$ 5,472,024	$ 843,556	$ 8,298,244	$ 4,031,216	$ 1,192,207	$ 7,591,000	$ 92,750	$ 39,178,412	2016
9	2015	$ 2,533,585	$ 2,091,747	$ 4,494,199	$ 1,702,275	$ 1,005,674	$ 2,167,017	$ 7,680,620	$ 110,592	$ 4,010,990	$ 10,878,519	$ 95,300	$ 3,564,216	$ 40,336,749	2015
10	2014	$ 2,078,895	$ 2,626,000	$ 6,654,385	$ 36,380	$ 2,859,745	$ 831,390	$ 1,937,547	$ 3,483,615	$ 2,140,838	$ 647,872	$ 1,137,422	$ 67,600	$ 24,503,703	2014
11	2013	$ 3,093,415	$ 1,090,780	$ 2,249,641	$ 1,796,440	$ 1,717,774	$ 1,044,612	$ 1,825,158	$ 3,733,815	$ 637,388	$ 869,792	$ 2,370,985	$ 823,075	$ 21,252,875	2013
12	2012	$ 217,787	$ 714,700	$ 80,019	$ 7,908,552	$ 3,674,515	$ 522,802	$ 2,398,672	$ 1,251,678	$ 28,843	$ 17,573	$ 9,400	$ 847,565	$ 17,672,106	2012
sales by month

Cell Formulas
Range	Formula
C4	C4	=+SUM(+MASTER!G3157:G3162)
D4	D4	=+SUM(+MASTER!G3163:G3171)
E4	E4	=+SUM(+MASTER!G3172:G3181)
F4	F4	=+SUM(+MASTER!G3182:G3188)
G4	G4	=+SUM(+MASTER!G3189:G3192)
H4	H4	=+SUM(+MASTER!G3193:G3201)
I4	I4	=+SUM(+MASTER!G3202:G3207)
J4	J4	=+SUM(+MASTER!G3208:G3215)
K4	K4	=+SUM(+MASTER!G3216:G3225)
L4	L4	=+SUM(+MASTER!G3226:G3234)
M4	M4	=+SUM(+MASTER!G3235:G3236)
N4	N4	=+SUM(+MASTER!G3238:G3239)
O4:O6,O11:O12	O4	=SUM(C4:N4)
P4:P12	P4	=+B4
C5	C5	=+SUM(+MASTER!G3054:G3062)
D5	D5	=+SUM(+MASTER!G3063:G3071)
E5	E5	=+SUM(+MASTER!G3072:G3086)
F5	F5	=+SUM(+MASTER!G3087:G3094)
G5	G5	=+SUM(+MASTER!G3095:G3105)+MASTER!G3109
H5	H5	=+SUM(+MASTER!G3106:G3108)+MASTER!G3110
I5	I5	=+SUM(+MASTER!G3112:G3123)
J5	J5	=+SUM(+MASTER!G3124:G3129)
K5	K5	=+SUM(+MASTER!G3130:G3133)
L5	L5	=+SUM(+MASTER!G3134:G3140)
M5	M5	=+SUM(+MASTER!G3141:G3148)
N5	N5	=+SUM(+MASTER!G3150:G3156)
C6	C6	=+SUM(MASTER!G2945:G2952)+MASTER!G2962
D6	D6	=+SUM(MASTER!G2953:G2958)+MASTER!G2961
E6	E6	=+SUM(MASTER!G2959:G2960)+SUM(MASTER!G2963:MASTER!G2970)+MASTER!G2973
F6	F6	=+SUM(MASTER!G2971:G2972)+SUM(MASTER!G2974:MASTER!G2984)
G6	G6	=+SUM(MASTER!G2985:G2994)
H6	H6	=+SUM(MASTER!G2995:G3005)
I6	I6	=+SUM(MASTER!G3006:G3015)
J6	J6	=+SUM(MASTER!G3016:G3021)
K6	K6	=+SUM(MASTER!G3022:G3030)
L6	L6	=+SUM(MASTER!G3031:G3035)
M6	M6	=+SUM(MASTER!G3036:G3041)
N6	N6	=+SUM(MASTER!G3043:G3053)
C7	C7	=+MASTER!G2836+MASTER!G2839
D7	D7	=+MASTER!G2837+MASTER!G2838+SUM(MASTER!G2840:G2844)
E7	E7	=+SUM(MASTER!G2845:G2856)
F7	F7	=+SUM(MASTER!G2857:G2861)
G7	G7	=+SUM(MASTER!G2862:G2870)
H7	H7	=+SUM(MASTER!G2871:G2884)
I7	I7	=+SUM(MASTER!G2885:G2891)
J7	J7	=+SUM(MASTER!G2892:G2904)
K7	K7	=+SUM(MASTER!G2905:G2915)
L7	L7	=+SUM(MASTER!G2916:G2930)
M7	M7	=+SUM(MASTER!G2931:G2937)
N7	N7	=+SUM(MASTER!G2939:G2944)
O7:O10	O7	=SUM(B7:N7)
C8	C8	=SUM(MASTER!G2753:G2760)
D8	D8	=SUM(MASTER!G2761:G2766)
E8	E8	=SUM(MASTER!G2767:G2772)
F8	F8	=SUM(MASTER!G2773:G2788)
G8	G8	=SUM(MASTER!H2789:MASTER!G2794)
H8	H8	=SUM(MASTER!G2795:G2803)
I8	I8	=SUM(MASTER!G2804:G2808)
J8	J8	=SUM(MASTER!G2809:G2819)
K8	K8	=SUM(MASTER!G2820:G2826)
L8	L8	=SUM(MASTER!G2827:G2830)-MASTER!G2829
M8	M8	=+MASTER!G2829+MASTER!G2831+MASTER!G2832
N8	N8	=+MASTER!G2834+MASTER!G2835
C9	C9	=SUM(MASTER!G2654:G2661)
D9	D9	=SUM(MASTER!G2662:G2664)
E9	E9	=SUM(MASTER!G2665:G2673)+MASTER!G2693+MASTER!G2682
F9	F9	=SUM(MASTER!G2674:G2681)+(MASTER!G2685+MASTER!G2686)+MASTER!G2707
G9	G9	=SUM(MASTER!G2683:G2684)+MASTER!G2687+MASTER!G2688+MASTER!G2689
H9	H9	=SUM(MASTER!G2690:G2706)-MASTER!G2693
I9	I9	=SUM(MASTER!G2708:G2718)
J9	J9	=SUM(MASTER!G2719:G2724)+MASTER!G2737
K9	K9	=SUM(MASTER!G2725:G2734)
L9	L9	=SUM(MASTER!G2735:G2746)-MASTER!G2737
M9	M9	=SUM(MASTER!G2747)
N9	N9	=SUM(MASTER!G2749:G2752)
C10	C10	=SUM(MASTER!G2570:G2574)+MASTER!G2576
D10	D10	=SUM(MASTER!G2575+MASTER!G2577+MASTER!G2578)
E10	E10	=SUM(MASTER!G2579:G2588)
F10	F10	=SUM(MASTER!G2589)
G10	G10	=SUM(MASTER!G2590:G2599)
H10	H10	=SUM(MASTER!G2600:G2603)
I10	I10	=SUM(MASTER!G2604:G2618)
J10	J10	=SUM(MASTER!G2619:G2628)+MASTER!G2634
K10	K10	=SUM(MASTER!G2629:G2637)-MASTER!G2634
L10	L10	=SUM(MASTER!G2638:G2644)
M10	M10	=SUM(MASTER!G2645:G2649)
N10	N10	=SUM(MASTER!G2651:G2653)
C11	C11	=SUM(MASTER!G2468:G2472)
D11	D11	=SUM(MASTER!G2473:G2478)
E11	E11	=SUM(MASTER!G2479:G2487)
F11	F11	=SUM(MASTER!G2488:G2495)
G11	G11	=SUM(MASTER!G2496:G2509)
H11	H11	=SUM(MASTER!G2510:G2518)
I11	I11	=SUM(MASTER!G2519:G2529)
J11	J11	=SUM(MASTER!G2530:G2544)
K11	K11	=SUM(MASTER!G2545:G2551)
L11	L11	=SUM(MASTER!G2552:G2557)
M11	M11	=SUM(MASTER!G2558:G2566)
N11	N11	=SUM(MASTER!G2568:G2569)
C12	C12	=SUM(MASTER!G2372:G2379)+MASTER!G2369
D12	D12	=SUM(MASTER!G2380:G2384)
E12	E12	=SUM(MASTER!G2385:G2391)
F12	F12	=SUM(MASTER!G2392:G2403)+MASTER!G2406
G12	G12	=SUM(MASTER!G2404:G2405)+SUM(MASTER!G2407:G2417)
H12	H12	=SUM(MASTER!G2418:G2422)+MASTER!G2432
I12	I12	=SUM(MASTER!G2423:G2431)+MASTER!G2433+MASTER!G2434+MASTER!G2435
J12	J12	=SUM(MASTER!G2436:G2445)
K12	K12	=SUM(MASTER!G2447:G2450)
L12	L12	=SUM(MASTER!G2451:G2454)
M12	M12	=SUM(MASTER!G2455:G2457)
N12	N12	=SUM(MASTER!G2459:G2467)

This is solved with =SUM(IF(ISNUMBER(MASTER!$E:$E),IF((MONTH(MASTER!$E:$E)='sales by month'!C$2)*(YEAR(MASTER!$E:$E)='sales by month'!$B4),MASTER!$G:$G))).

Other 2 items above are still not solved thanks. PS is there away to remove a post or edit a thread if i am the author? I would like to remove parts that are no longer relevant. Thanks!

 

[keef2]

got this figured too thanks!

 

[Peter_SSs]

got this figured too thanks!

Good to hear you solved it.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[keef2]

I just added a subtotal row for the years and then used excel to autogroup.

 

[Peter_SSs]

PS is there away to remove a post or edit a thread if i am the author?

You cannot remove posts. You can edit an individual post within 10 minutes after posting.

I would like to remove parts that are no longer relevant.

Allowing that, or complete post removal, can make threads subsequently meaningless for future readers - hence the limited amount of editing allowed.