Subtotal when value is in different table

[ttleigh]

I am trying to calculate subtotal when value of the sort is in different table.

Column F has the text to sort (8 different choices)
Value for each choice is in table "NorCalMkt" (Trade Area is the choice and Mkt is the value)

How do I get the value for Trade Name when I sort in Column F?

2021 FY Performance with Market Share.xlsx
	O	P	Q	R	S
1	Trade Area by Name	TA #	EL Vol	21 1H Mkt	21 FY Est
2	Reno	113	5,813	22,060	44,119
3	Sacramento	114	2,069	38,224	76,447
4	Stockton	116	2,649	13,368	26,736
5	Fresno	117	814	20,937	41,875
6	Santa Rosa/Vallejo	169	1,125	14,312	28,625
7	San Francisco	170	1,722	20,363	40,726
8	Oakland/East Bay	171	19,048	59,298	118,596
9	San Jose/Monterey	172	1,992	31,959	63,919
10	EL TOTAL		35,231	220,521	441,043
Sheet1

Cell Formulas
Range	Formula
S2:S10	S2	=([@[21 1H Mkt]]/6)*12

2021 FY Performance with Market Share.xlsx
	C	D	E	F	G	H	I	J	K	L
1	Sold To Customer City	Sold To Customer State	County	Trade Area	DROP	STOCK	Grand Total	FY in (000)	Avail Mkt (000)	Share
2	EMERYVILLE	CA	Alameda	Oakland/East Bay		$ 1,911.75	$ 1,911.75	$ 2	$ 118,596	0.00%
3	HAYWARD	CA	Alameda	Oakland/East Bay	$ 2,415,236.12	$ 4,649,581.78	$ 7,064,817.90	$ 7,065	$ 118,596	5.96%
4	GOSHEN	CA	Tulare	Fresno	$ 68,650.35		$ 68,650.35	$ 69	$ 41,875	0.16%
5	BURLINGAME	CA	San Mateo	San Francisco	$ 1,469,991.06	$ 35,111.68	$ 1,505,102.74	$ 1,505	$ 40,726	3.70%
6	FRESNO	CA	Fresno	Fresno	$ 41,695.36	$ 4,391.36	$ 46,086.72	$ 46	$ 41,875	0.11%
7	RENO	NV	Washoe	Reno	$ 5,237,007.68	$ 4,803.61	$ 5,241,811.29	$ 5,242	$ 44,119	11.88%
8	UNION CITY	CA	Alameda	Oakland/East Bay		$ 40,998.16	$ 40,998.16	$ 41	$ 118,596	0.03%
9	PLEASANTON	CA	Alameda	Oakland/East Bay	$ 22,573.77		$ 22,573.77	$ 23	$ 118,596	0.02%
10	PLEASANTON	CA	Alameda	Oakland/East Bay	$ 9,470.49		$ 9,470.49	$ 9	$ 118,596	0.01%
11	MODESTO	CA	Stanislaus	Stockton		$ 39,971.56	$ 39,971.56	$ 40	$ 26,736	0.15%
12	SAN JOSE	CA	Santa Clara	San Jose/Monterey	$ 2,368,306.64	$ (482,244.74)	$ 1,886,061.90	$ 1,886	$ 63,919	2.95%
13	ROSEVILLE	CA	Placer	Sacramento	$ 103,159.68		$ 103,159.68	$ 103	$ 76,447	0.13%
14	ROSEVILLE	CA	Placer	Sacramento	$ 34,968.88	$ 27,677.25	$ 62,646.13	$ 63	$ 76,447	0.08%
15	REDWOOD CITY	CA	San Mateo	San Francisco		$ -	$ -	$ -	$ 40,726	0.00%
16	RICHMOND	CA	Contra Costa	Oakland/East Bay	$ 46,498.61	$ (4,216.03)	$ 42,282.58	$ 42	$ 118,596	0.04%
17	SANTA CLARA	CA	Santa Clara	San Jose/Monterey	$ 72,372.04	$ 358,825.35	$ 431,197.39	$ 431	$ 63,919	0.67%
18	SAN JOSE	CA	Santa Clara	San Jose/Monterey	$ 126,064.98	$ (564.44)	$ 125,500.54	$ 126	$ 63,919	0.20%
19	HAYWARD	CA	Alameda	Oakland/East Bay		$ 17,945.07	$ 17,945.07	$ 18	$ 118,596	0.02%
20	SUNNYVALE	CA	Santa Clara	San Jose/Monterey	$ -		$ -	$ -	$ 63,919	0.00%
21	SANTA CLARA	CA	Santa Clara	San Jose/Monterey	$ 48,175.20	$ 431,563.15	$ 479,738.35	$ 480	$ 63,919	0.75%
22	LIVERMORE	CA	Alameda	Oakland/East Bay		$ (6,334.96)	$ (6,334.96)	$ (6)	$ 118,596	-0.01%
23	MARTINEZ	CA	Contra Costa	Oakland/East Bay	$ 8,516.40	$ 2,554.30	$ 11,070.70	$ 11	$ 118,596	0.01%
24	OAKLAND	CA	Alameda	Oakland/East Bay	$ 6,458.32		$ 6,458.32	$ 6	$ 118,596	0.01%
25	SAN LEANDRO	CA	Alameda	Oakland/East Bay	$ 236.16		$ 236.16	$ -	$ 118,596	0.00%
26	RANCHO CORDOVA	CA	Sacramento	Sacramento	$ 34,536.89	$ 403.40	$ 34,940.29	$ 35	$ 76,447	0.05%
27	SPARKS	NV	Washoe	Reno		$ 4,057.36	$ 4,057.36	$ 4	$ 44,119	0.01%
28	MILPITAS	CA	Santa Clara	San Jose/Monterey	$ 444,226.69	$ 51,107.95	$ 495,334.64	$ 495	$ 63,919	0.77%
29	RANCHO CORDOVA	CA	Sacramento	Sacramento	$ 388,621.98	$ 2,723.84	$ 391,345.82	$ 391	$ 76,447	0.51%
30	REDDING	CA	Shasta	Sacramento	$ 6,734.34	$ 86,314.19	$ 93,048.53	$ 93	$ 76,447	0.12%
31	SAN FRANCISCO	CA	San Francisco	San Francisco	$ 61,282.84	$ 8,051.45	$ 69,334.29	$ 69	$ 40,726	0.17%
32	SACRAMENTO	CA	Sacramento	Sacramento	$ -		$ -	$ -	$ 76,447	0.00%
33	ESCALON	CA	San Joaquin	Stockton		$ -	$ -	$ -	$ 26,736	0.00%
34	SANTA ROSA	CA	Sonoma	Santa Rosa/Vallejo	$ 1,626,360.01	$ 61,937.19	$ 1,688,297.20	$ 1,688	$ 28,625	5.90%
35	SAN RAFAEL	CA	Marin	San Francisco	$ 33,448.92	$ 33,755.63	$ 67,204.55	$ 67	$ 40,726	0.16%
36	SANTA CLARA	CA	Santa Clara	San Jose/Monterey	$ 16,251.64	$ 1,772.30	$ 18,023.94	$ 18	$ 63,919	0.03%
37	SAN LEANDRO	CA	Alameda	Oakland/East Bay	$ 9,785.06		$ 9,785.06	$ 10	$ 118,596	0.01%
38	STOCKTON	CA	San Joaquin	Stockton		$ 2,016.38	$ 2,016.38	$ 2	$ 26,736	0.01%
Sheet1

Cell Formulas
Range	Formula
J2:J38	J2	=ROUND(I2/1000,0)
K2:K38	K2	=VLOOKUP(F2,NorCalMkt,5,FALSE)
L2:L38	L2	=J2/K2

 

[StephenCrump]

Welcome to the Forum!

It's not clear what results you expect to see for the sample data posted. Can you please explain in more detail?

 

[ttleigh]

Thank you.
When I filter by Trade Area, I would like the result for Column Avail Mkt (000) to pull from Table NorCalMkt "Trade Area by Name" with the value in column 5 "21 FY Est" of same table

 

[StephenCrump]

Sorry, I'm still not clear what you're looking for.

If I filter for "San Francisco", say in column F, I get this. Which sounds like the result you're describing in Post #3?

	A	B	C	D	E	F	G	H	I	J	K	L
1			Sold To Customer City	Sold To Customer State	County	Trade Area	DROP	STOCK	Grand Total	FY in (000)	Avail Mkt (000)	Share
5			BURLINGAME	CA	San Mateo	San Francisco	$1,469,991	$35,112	$1,505,103	$1,505	$40,726	3.70%
15			REDWOOD CITY	CA	San Mateo	San Francisco		$0	$0	$0	$40,726	0.00%
31			SAN FRANCISCO	CA	San Francisco	San Francisco	$61,283	$8,051	$69,334	$69	$40,726	0.17%
35			SAN RAFAEL	CA	Marin	San Francisco	$33,449	$33,756	$67,205	$67	$40,726	0.16%
39
Sheet1

Cell Formulas
Range	Formula
J5,J15,J31,J35	J5	=ROUND(I5/1000,0)
K5,K15,K31,K35	K5	=VLOOKUP(F5,Table1,5,FALSE)
L5,L15,L31,L35	L5	=J5/K5

 

[ttleigh]

I am sorry. I wasn't clear.
I would like the Grand Total of "Avail Mkt(000)" be the total from table "NorCalMkt".
If I filter "fresno" in Trade area, the total will be 41,875 (Not the sum of the column).
Or "sacremento" the total is 76,447.

 

[StephenCrump]

So something like this, perhaps, assuming you filtered on both areas?

	A	B	C	D	E	F	G	H	I	J	K	L
1			Sold To Customer City	Sold To Customer State	County	Trade Area	DROP	STOCK	Grand Total	FY in (000)	Avail Mkt (000)	Share
4			GOSHEN	CA	Tulare	Fresno	$68,650		$68,650	$69	$41,875	0.16%
6			FRESNO	CA	Fresno	Fresno	$41,695	$4,391	$46,087	$46	$41,875	0.11%
13			ROSEVILLE	CA	Placer	Sacramento	$103,160		$103,160	$103	$76,447	0.13%
14			ROSEVILLE	CA	Placer	Sacramento	$34,969	$27,677	$62,646	$63	$76,447	0.08%
26			RANCHO CORDOVA	CA	Sacramento	Sacramento	$34,537	$403	$34,940	$35	$76,447	0.05%
29			RANCHO CORDOVA	CA	Sacramento	Sacramento	$388,622	$2,724	$391,346	$391	$76,447	0.51%
30			REDDING	CA	Shasta	Sacramento	$6,734	$86,314	$93,049	$93	$76,447	0.12%
32			SACRAMENTO	CA	Sacramento	Sacramento	$0		$0	$0	$76,447	0.00%
39
40										Grand total	$118,322
Sheet1

Cell Formulas
Range	Formula
J4,J6,J13:J14,J26,J29:J30,J32	J4	=ROUND(I4/1000,0)
K4,K6,K13:K14,K26,K29:K30,K32	K4	=VLOOKUP(F4,NorCalMkt,5,FALSE)
L4,L6,L13:L14,L26,L29:L30,L32	L4	=J4/K4
K40	K40	=LET(MyCol,F2:F38,s,SEQUENCE(ROWS(MyCol)),SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(MyCol,s-1,,1)),MATCH(MyCol,MyCol,)),s),VLOOKUP(MyCol,NorCalMkt,5,))))

Named Ranges
Name	Refers To	Cells
NorCalMkt	=Sheet1!$N$1:$R$10	K40, K32, K29:K30, K26, K13:K14, K6, K4

 

[ttleigh]

Thank you! This worked and you are my hero!

 

[StephenCrump]

No problem, I'm glad we could help.