# Subtotal when value is in different table

#### ttleigh

##### New Member
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
OPQRS
1Trade Area by NameTA #EL Vol21 1H Mkt21 FY Est
2Reno1135,81322,06044,119
3Sacramento1142,06938,22476,447
4Stockton1162,64913,36826,736
5Fresno11781420,93741,875
6Santa Rosa/Vallejo1691,12514,31228,625
7San Francisco1701,72220,36340,726
8Oakland/East Bay17119,04859,298118,596
9San Jose/Monterey1721,99231,95963,919
10EL TOTAL35,231220,521441,043
Sheet1
Cell Formulas
RangeFormula
S2:S10S2=([@[21 1H Mkt]]/6)*12

2021 FY Performance with Market Share.xlsx
CDEFGHIJKL
1Sold To Customer CitySold To Customer StateCountyTrade Area DROP STOCK Grand Total FY in (000) Avail Mkt (000) Share
2EMERYVILLECAAlamedaOakland/East Bay\$ 1,911.75\$ 1,911.75\$ 2\$ 118,5960.00%
3HAYWARDCAAlamedaOakland/East Bay\$ 2,415,236.12\$ 4,649,581.78\$ 7,064,817.90\$ 7,065\$ 118,5965.96%
4GOSHENCATulareFresno\$ 68,650.35\$ 68,650.35\$ 69\$ 41,8750.16%
5BURLINGAMECASan MateoSan Francisco\$ 1,469,991.06\$ 35,111.68\$ 1,505,102.74\$ 1,505\$ 40,7263.70%
6FRESNOCAFresnoFresno\$ 41,695.36\$ 4,391.36\$ 46,086.72\$ 46\$ 41,8750.11%
7RENONVWashoeReno\$ 5,237,007.68\$ 4,803.61\$ 5,241,811.29\$ 5,242\$ 44,11911.88%
8UNION CITYCAAlamedaOakland/East Bay\$ 40,998.16\$ 40,998.16\$ 41\$ 118,5960.03%
9PLEASANTONCAAlamedaOakland/East Bay\$ 22,573.77\$ 22,573.77\$ 23\$ 118,5960.02%
10PLEASANTONCAAlamedaOakland/East Bay\$ 9,470.49\$ 9,470.49\$ 9\$ 118,5960.01%
11MODESTOCAStanislausStockton\$ 39,971.56\$ 39,971.56\$ 40\$ 26,7360.15%
12SAN JOSECASanta ClaraSan Jose/Monterey\$ 2,368,306.64\$ (482,244.74)\$ 1,886,061.90\$ 1,886\$ 63,9192.95%
13ROSEVILLECAPlacerSacramento\$ 103,159.68\$ 103,159.68\$ 103\$ 76,4470.13%
14ROSEVILLECAPlacerSacramento\$ 34,968.88\$ 27,677.25\$ 62,646.13\$ 63\$ 76,4470.08%
15REDWOOD CITYCASan MateoSan Francisco\$ -\$ -\$ -\$ 40,7260.00%
16RICHMONDCAContra CostaOakland/East Bay\$ 46,498.61\$ (4,216.03)\$ 42,282.58\$ 42\$ 118,5960.04%
17SANTA CLARACASanta ClaraSan Jose/Monterey\$ 72,372.04\$ 358,825.35\$ 431,197.39\$ 431\$ 63,9190.67%
18SAN JOSECASanta ClaraSan Jose/Monterey\$ 126,064.98\$ (564.44)\$ 125,500.54\$ 126\$ 63,9190.20%
19HAYWARDCAAlamedaOakland/East Bay\$ 17,945.07\$ 17,945.07\$ 18\$ 118,5960.02%
20SUNNYVALECASanta ClaraSan Jose/Monterey\$ -\$ -\$ -\$ 63,9190.00%
21SANTA CLARACASanta ClaraSan Jose/Monterey\$ 48,175.20\$ 431,563.15\$ 479,738.35\$ 480\$ 63,9190.75%
22LIVERMORECAAlamedaOakland/East Bay\$ (6,334.96)\$ (6,334.96)\$ (6)\$ 118,596-0.01%
23MARTINEZCAContra CostaOakland/East Bay\$ 8,516.40\$ 2,554.30\$ 11,070.70\$ 11\$ 118,5960.01%
24OAKLANDCAAlamedaOakland/East Bay\$ 6,458.32\$ 6,458.32\$ 6\$ 118,5960.01%
25SAN LEANDROCAAlamedaOakland/East Bay\$ 236.16\$ 236.16\$ -\$ 118,5960.00%
26RANCHO CORDOVACASacramentoSacramento\$ 34,536.89\$ 403.40\$ 34,940.29\$ 35\$ 76,4470.05%
27SPARKSNVWashoeReno\$ 4,057.36\$ 4,057.36\$ 4\$ 44,1190.01%
28MILPITASCASanta ClaraSan Jose/Monterey\$ 444,226.69\$ 51,107.95\$ 495,334.64\$ 495\$ 63,9190.77%
29RANCHO CORDOVACASacramentoSacramento\$ 388,621.98\$ 2,723.84\$ 391,345.82\$ 391\$ 76,4470.51%
30REDDINGCAShastaSacramento\$ 6,734.34\$ 86,314.19\$ 93,048.53\$ 93\$ 76,4470.12%
31SAN FRANCISCOCASan FranciscoSan Francisco\$ 61,282.84\$ 8,051.45\$ 69,334.29\$ 69\$ 40,7260.17%
32SACRAMENTOCASacramentoSacramento\$ -\$ -\$ -\$ 76,4470.00%
33ESCALONCASan JoaquinStockton\$ -\$ -\$ -\$ 26,7360.00%
34SANTA ROSACASonomaSanta Rosa/Vallejo\$ 1,626,360.01\$ 61,937.19\$ 1,688,297.20\$ 1,688\$ 28,6255.90%
35SAN RAFAELCAMarinSan Francisco\$ 33,448.92\$ 33,755.63\$ 67,204.55\$ 67\$ 40,7260.16%
36SANTA CLARACASanta ClaraSan Jose/Monterey\$ 16,251.64\$ 1,772.30\$ 18,023.94\$ 18\$ 63,9190.03%
37SAN LEANDROCAAlamedaOakland/East Bay\$ 9,785.06\$ 9,785.06\$ 10\$ 118,5960.01%
38STOCKTONCASan JoaquinStockton\$ 2,016.38\$ 2,016.38\$ 2\$ 26,7360.01%
Sheet1
Cell Formulas
RangeFormula
J2:J38J2=ROUND(I2/1000,0)
K2:K38K2=VLOOKUP(F2,NorCalMkt,5,FALSE)
L2:L38L2=J2/K2

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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?

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

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?

ABCDEFGHIJKL
1Sold To Customer CitySold To Customer StateCountyTrade Area DROP STOCK Grand Total FY in (000) Avail Mkt (000) Share
5BURLINGAMECASan MateoSan Francisco\$1,469,991\$35,112\$1,505,103\$1,505\$40,7263.70%
15REDWOOD CITYCASan MateoSan Francisco\$0\$0\$0\$40,7260.00%
31SAN FRANCISCOCASan FranciscoSan Francisco\$61,283\$8,051\$69,334\$69\$40,7260.17%
35SAN RAFAELCAMarinSan Francisco\$33,449\$33,756\$67,205\$67\$40,7260.16%
39
Sheet1
Cell Formulas
RangeFormula
J5,J15,J31,J35J5=ROUND(I5/1000,0)
K5,K15,K31,K35K5=VLOOKUP(F5,Table1,5,FALSE)
L5,L15,L31,L35L5=J5/K5

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.

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

ABCDEFGHIJKL
1Sold To Customer CitySold To Customer StateCountyTrade Area DROP STOCK Grand Total FY in (000) Avail Mkt (000) Share
4GOSHENCATulareFresno\$68,650\$68,650\$69\$41,8750.16%
6FRESNOCAFresnoFresno\$41,695\$4,391\$46,087\$46\$41,8750.11%
13ROSEVILLECAPlacerSacramento\$103,160\$103,160\$103\$76,4470.13%
14ROSEVILLECAPlacerSacramento\$34,969\$27,677\$62,646\$63\$76,4470.08%
26RANCHO CORDOVACASacramentoSacramento\$34,537\$403\$34,940\$35\$76,4470.05%
29RANCHO CORDOVACASacramentoSacramento\$388,622\$2,724\$391,346\$391\$76,4470.51%
30REDDINGCAShastaSacramento\$6,734\$86,314\$93,049\$93\$76,4470.12%
32SACRAMENTOCASacramentoSacramento\$0\$0\$0\$76,4470.00%
39
40Grand total\$118,322
Sheet1
Cell Formulas
RangeFormula
J4,J6,J13:J14,J26,J29:J30,J32J4=ROUND(I4/1000,0)
K4,K6,K13:K14,K26,K29:K30,K32K4=VLOOKUP(F4,NorCalMkt,5,FALSE)
L4,L6,L13:L14,L26,L29:L30,L32L4=J4/K4
K40K40=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
NameRefers ToCells
NorCalMkt=Sheet1!\$N\$1:\$R\$10K40, K32, K29:K30, K26, K13:K14, K6, K4

Last edited:
Thank you! This worked and you are my hero!

No problem, I'm glad we could help.

1,207,097
Messages
6,076,556
Members
446,213
Latest member
bettigb

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back