How to sum with multiple criteria in 2 different columns? - census data

[thirty6chambers]

I am trying to organize some census data and am looking for a more optimized solution. I have a table that includes data for 12 different counties and want to find the number of "Manufacturing" establishments that are under 20 employees and the number of "Manufacturing" establishments between 20 to 999 employees. Any help or guidance would be greatly appreciated, thanks!

CBP2022.NewOrleans.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Geographic Area Name	Meaning of NAICS Code	Meaning of Employment size of establishments code	Year	Number of establishments	Number of employees
2	Jefferson Parish, Louisiana	Total for all sectors	All establishments	2022	11,729	174,764			Establishments with 1 to 4 employees	20726		SMB 1-19	33624
3	Jefferson Parish, Louisiana	Total for all sectors	Establishments with less than 5 employees	2022	5,995	N			Establishments with 5 to 9 employees	7658		Mid 20-999	11109
4	Jefferson Parish, Louisiana	Total for all sectors	Establishments with 5 to 9 employees	2022	2,323	N			Establishments with 10 to 19 employees	5240
5	Jefferson Parish, Louisiana	Total for all sectors	Establishments with 10 to 19 employees	2022	1,600	N			Establishments with 20 to 49 employees	3815
6	Jefferson Parish, Louisiana	Total for all sectors	Establishments with 20 to 49 employees	2022	1,162	N			Establishments with 50 to 99 employees	1211
7	Jefferson Parish, Louisiana	Total for all sectors	Establishments with 50 to 99 employees	2022	375	N			Establishments with 100 to 249 employees	648
8	Jefferson Parish, Louisiana	Total for all sectors	Establishments with 100 to 249 employees	2022	214	N			Establishments with 250 to 499 employees	153
9	Jefferson Parish, Louisiana	Total for all sectors	Establishments with 250 to 499 employees	2022	41	N			Establishments with 500 to 999 employees	42
10	Jefferson Parish, Louisiana	Total for all sectors	Establishments with 500 to 999 employees	2022	13	N
11	Jefferson Parish, Louisiana	Total for all sectors	Establishments with 1,000 employees or more	2022	6	N
12	Jefferson Parish, Louisiana	Total for all sectors	Establishments with 1,000 to 1,499 employees	2022	3	N
13	Jefferson Parish, Louisiana	Agriculture, forestry, fishing and hunting	All establishments	2022	14	34
14	Jefferson Parish, Louisiana	Agriculture, forestry, fishing and hunting	Establishments with less than 5 employees	2022	12	N			Utilities	219
15	Jefferson Parish, Louisiana	Mining, quarrying, and oil and gas extraction	All establishments	2022	26	317			Construction	4104
16	Jefferson Parish, Louisiana	Mining, quarrying, and oil and gas extraction	Establishments with less than 5 employees	2022	15	N			Manufacturing	#VALUE!
17	Jefferson Parish, Louisiana	Mining, quarrying, and oil and gas extraction	Establishments with 5 to 9 employees	2022	3	N			Wholesale trade	#VALUE!
18	Jefferson Parish, Louisiana	Mining, quarrying, and oil and gas extraction	Establishments with 10 to 19 employees	2022	4	N			Retail trade
19	Jefferson Parish, Louisiana	Utilities	All establishments	2022	22	566			Transportation and warehousing
20	Jefferson Parish, Louisiana	Utilities	Establishments with less than 5 employees	2022	10	N			Information
21	Jefferson Parish, Louisiana	Utilities	Establishments with 5 to 9 employees	2022	3	N			Finance and insurance
22	Jefferson Parish, Louisiana	Utilities	Establishments with 20 to 49 employees	2022	4	N			Real estate and rental and leasing
23	Jefferson Parish, Louisiana	Utilities	Establishments with 50 to 99 employees	2022	5	N			Professional, scientific, and technical services
24	Jefferson Parish, Louisiana	Construction	All establishments	2022	1,049	10,218			Management of companies and enterprises
25	Jefferson Parish, Louisiana	Construction	Establishments with less than 5 employees	2022	619	N			Administrative and support and waste management and remediation services
26	Jefferson Parish, Louisiana	Construction	Establishments with 5 to 9 employees	2022	190	N			Educational services
27	Jefferson Parish, Louisiana	Construction	Establishments with 10 to 19 employees	2022	114	N			Health care and social assistance
28	Jefferson Parish, Louisiana	Construction	Establishments with 20 to 49 employees	2022	85	N			Arts, entertainment, and recreation
29	Jefferson Parish, Louisiana	Construction	Establishments with 50 to 99 employees	2022	26	N			Accommodation and food services
30	Jefferson Parish, Louisiana	Construction	Establishments with 100 to 249 employees	2022	15	N			Other services (except public administration)
31	Jefferson Parish, Louisiana	Manufacturing	All establishments	2022	260	7,479
32	Jefferson Parish, Louisiana	Manufacturing	Establishments with less than 5 employees	2022	102	N
Data

Cell Formulas
Range	Formula
M2	M2	=SUM(J2:J4)
M3	M3	=SUM(J4:J9)
J2:J9	J2	=SUMIFS(Table1[Number of establishments],Table1[Meaning of Employment size of establishments code],C3,Table1[Meaning of NAICS Code],B3)
J14	J14	=SUMIFS(E2:E1193,Table1[Meaning of NAICS Code],B19)
J15	J15	=SUMIFS(E3:E1194,Table1[Meaning of NAICS Code],B24)
J16	J16	=SUMIFS(Table1[[#All],[Number of establishments]],Table1[Meaning of NAICS Code],"Manufacturing")
J17	J17	=SUMIFS(Table1[Number of establishments],B4:B1193,B39)

 

[hagia_sofia]

Hello, please test this if it works as intended:

=LET(
a,C2:C32,
b,IFNA(IF(a="Establishments with less than 5 employees",5,--(TEXTBEFORE(TEXTAFTER(a,"Establishments with ")," to"))),""),
SUM(FILTER(E2:E32,(B2:B32="Manufacturing")*(b>=1)*(b<20),0)))

=LET(
a,C2:C32,
b,IFNA(IF(a="Establishments with less than 5 employees",5,--(TEXTBEFORE(TEXTAFTER(a,"Establishments with ")," to"))),""),
SUM(FILTER(E2:E32,(B2:B32="Manufacturing")*(b>=20)*(b<1000),0)))