Spreadsheet: 106478d1303761220-vlookup-with-a-sum-calculation-test
Sheet: Summary by Country 6%

Region |  | 0.07 | 0.05 | 50/50 | Current Total | New 5% Model | Diff | Diff %
ASIA | Import |  | 15071344.6045 | 607750.0150000001 |  | 15726339.1355 |  |  | Commission given to destination (Cost)
 | Export |  | -67957567.707 | -5803358.855 |  | -77130484.0109998 |  |  | Commission received as destination (Rev)
EUROPE/AFRICA | Import | 9978013.648400003 | 17374965.51849999 | 1999601.639999999 |  | 26654448.00700001
 | Export | -5660969.1264 | -2785473.0315 | -4072407.62 |  | -10685573.801
NMEIS | Import |  |  | 6971365.225000003 |  | 7255428.458500004
 | Export |  |  | -3049621.145 |  | -4905579.5495
NORTH AMERICA | Import | 5660969.126399999 | 48360468.999 | 3685820.540000001 |  | 56833689.19249997
 | Export | -9978013.6484 | -10063738.3835 | -9862484.38 |  | -25586892.587
SOUTH PACIFIC | Import |  |  | 4160429.154999999 |  | 4370068.073
 | Export |  |  | -711017.655 |  | -516435.897
SOUTH/CENTRAL AMERICA | Import |  |  | 7362342.135000004 |  | 8953514.966999998
 | Export |  |  | -1288419.055 |  | -968521.988
Comparison by Country
Region |  | 0.07 | 0.05 | 50/50 | Current Total | New 6% Model | Diff | Diff % | Row Labels | Sum of Dest New 6% Comm
ARGENTINA |  |  |  |  |  |  |  |  | ARGENTINA | 83675.8428
 | Export |  |  | 76726.25500000003 |  | 69729.86900000002 |  |  | AUSTRALIA | 487322.5122
 | Import |  |  | 444610.63 |  | 652868.676 |  |  | AUSTRIA | 200248.884
 |  |  |  |  |  |  |  |  | BAHRAIN | 20337.6912
AUSTRALIA |  |  |  |  |  |  |  |  | BANGLADESH | 994078.3008
 | Export |  |  | 599093.55 |  | 406102.0935 |  |  | BELGIUM | 494331.2898
 | Import |  |  | 3816459.105 |  | 4056916.8215 |  |  | BRAZIL | 557747.9928
 |  |  |  |  |  |  |  |  | BULGARIA | 802.9734
AUSTRIA |  |  |  |  |  |  |  |  | CANADA | 674709.4182
 | Export | 104125.9576 | 56734.41 | 44381.57000000002 | 205241.9376000001 | 166874.07 |  |  | CHILE | 31369.092
 | Import | 70486.4335 | 154143.0625 | 3573.16 | 228202.656 | 208183.8615 |  |  | CHINA | 34851091.31099999
 |  | 33639.52410000001 | 97408.6525000001 | 40808.41000000002 | 22960.7184 | 41309.7915 |  |  | CHINA (TAIWAN) | 3832574.5338
BAHRAIN |  |  |  |  |  |  |  |  | COLOMBIA | 61492.0362
 | Export |  |  | 6905.760000000004 | 6905.760000000004 | 16948.076 |  |  | COSTA RICA | 269793.129
 | Import |  |  | 72731.42 | 72731.42 | 64178.0855 |  |  | CZECH REPUBLIC | 244572.4674
 |  |  |  | 65825.66 | 65825.66 | 47230.0095 |  |  | EGYPT | 257961.36
BANGLADESH |  |  |  |  |  |  |  |  | FINLAND | 147497.9736
 | Export |  | 807661.5005 | 21334.35999999999 | 828995.8605 | 828398.5839999999 |  |  | FRANCE | 1360244.3124
 | Import |  | 91792.5115 | 309.035 | 92101.5465 | 94063.458 |  |  | GERMANY | 2556698.669999999
 |  |  | 715868.989 | 21025.32499999999 | 736894.3139999999 | 734335.1259999999 |  |  | GREECE | 56014.7436
BELGIUM |  |  |  |  |  |  |  |  | HONG KONG | 28828509.5466
 | Export | 182973.007 | 102900.994 | 186851.495 | 472725.496 | 411942.7415000001 |  |  | HUNGARY | 185859.1986
 | Import | 882433.9041 | 684376.357 | 66505.83 | 1633316.0911 | 1375465.329 |  |  | INDIA | 4086723.8538
 |  | 699460.8971 | 581475.363 | 120345.665 | 1160590.5951 | 963522.5875 |  |  | INDONESIA | 1741672.6374
BRAZIL |  |  |  |  |  |  |  |  | IRELAND | 794081.7672000004
 | Export |  |  | 557599.1700000004 | 557599.1700000004 | 464789.994 |  |  | ITALY | 1223278.5696
 | Import | 0 | 0 | 5704470.63 | 5704470.63 | 6671168.3975 |  |  | JAPAN | 2756834.862
 |  |  |  | 5146871.46 | 5146871.46 | 6206378.4035 |  |  | JORDAN | 314202.5712
BULGARIA |  |  |  |  |  |  |  |  | [NAME] (CAMBODIA) | 439719.4524
 | Export |  | 416.225 | 1018.31 | 1434.535 | 669.1445000000001 |  |  | KOREA, REPUBLIC OF | 2782145.778
 | Import | 3313.3716 | 2964.4065 | 1983.89 | 8261.6681 | 8343.629 |  |  | KUWAIT | 5573.1132
 |  | 3313.3716 | 2548.1815 | 965.579999999999 | 6827.1331 | 7674.4845 |  |  | LEBANON | 35485.39199999999
CANADA |  |  |  |  |  |  |  |  | MACAO (MACAU) | 18789.4008
 | Export |  | 386268.4239999999 | 197295.1900000001 | 583563.6140000001 | 562257.8485000001 |  |  | MADAGASCAR | 6943.701
 | Import | 0 | 1863537.2575 | 71959.285 | 1935496.5425 | 1977691.1765 |  |  | MALAYSIA | 4655330.547599999
 |  |  | 1477268.8335 | 125335.9050000001 | 1351932.9285 | 1415433.328 |  |  | MEXICO | 1651355.328
CHILE |  |  |  |  |  |  |  |  | NETHERLANDS | 1307779.575
 | Export |  |  | 42357.67499999998 | 42357.67499999998 | 26140.91 |  |  | NEW ZEALAND | 132400.5642
 | Import | 0 | 0 | 525900.925 | 525900.925 | 818197.259 |  |  | NORWAY | 97591.7292
 |  |  |  | 483543.25 | 483543.25 | 792056.349 |  |  | OMAN | 7442.01
CHINA |  |  |  |  |  |  |  |  | PAKISTAN | 492241.2666000001
 | Export |  | 23684958.202 | 2519047.96 | 26204006.162 | 29042576.0925 |  |  | PERU | 158148.2928
 | Import | 0 | 4864794.578 | 175227.745 | 5040022.323 | 5061012.135 |  |  | PHILIPPINES | 875457.4277999998
 |  |  | 18820163.62400001 | 2343820.215 | 21163983.83900001 | 23981563.9575 |  |  | POLAND | 184734.8322
CHINA (TAIWAN) |  |  |  |  |  |  |  |  | PORTUGAL | 100548.2934
 | Export |  | 3009633.8665 | 180324.005 | 3189957.8715 | 3193812.111500001 |  |  | PUERTO RICO | 442944.8778
 | Import |  | 708067.583 | 28766.45 | 736834.033 | 743108.014 |  |  | QATAR | 6000.2544
 |  |  | 2301566.2835 | 151557.555 | 2453123.838500001 | 2450704.097500001 |  |  | ROMANIA | 41204.4924
COLOMBIA |  |  |  |  |  |  |  |  | SAUDI ARABIA | 20244.9768
 | Export |  |  | 49448.35500000001 | 49448.35500000001 | 51243.36350000001 |  |  | SINGAPORE | 3104362.4556
 | Import | 0 | 0 | 503324.805 | 503324.805 | 620465.3055 |  |  | SOUTH AFRICA | 322085.7389999999
 |  |  |  | 453876.45 | 453876.45 | 569221.942 |  |  | SPAIN | 848130.2112
CZECH REPUBLIC |  |  |  |  |  |  |  |  | SRI LANKA | 265657.0758
 | Export | 163403.3814 | 58636.945 | 28453.2 | 250493.5264 | 203810.3895 |  |  | SWEDEN | 471405.7032
 | Import | 275602.2829 | 2030668.433 | 24624.73 | 2330895.4459 | 2254187.482 |  |  | SWITZERLAND | 343728.8718
 |  | 112198.9015 | 1972031.488 | 3828.46999999999 | 2080401.9195 | 2050377.0925 |  |  | THAILAND | 3055227.7602
EGYPT |  |  |  |  |  |  |  |  | TURKEY | 440371.2582
 | Export |  |  | 214861.7349999999 | 214861.7349999999 | 214967.8 |  |  | UNITED ARAB EMIRATES | 144096.9684
 | Import | 0 | 0 | 198480.625 | 198480.625 | 222006.121 |  |  | UNITED KINGDOM | 1890824.9868
 |  |  |  | 16381.10999999993 | 16381.10999999993 | 7038.32099999999 |  |  | UNITED STATES | 27935261.4804
FINLAND |  |  |  |  |  |  |  |  | VIETNAM | 4355129.7234
 | Export | 44231.95280000001 | 50105.89900000001 | 31695.97500000003 | 126033.8268 | 122914.978
 | Import | 90992.1131 | 36098.9335 | 21594.2 | 148685.2466 | 116897.451
 |  | 46760.1603 | 14006.96550000002 | 10101.77500000004 | 22651.4197999999 | 6017.527000000031
FRANCE
 | Export | 643365.2932000001 | 285643.5294999999 | 409392.81 | 1338401.6327 | 1133536.927
 | Import | 1152744.425 | 1801412.2205 | 143674.28 | 3097830.9255 | 2897479.2535
 |  | 509379.1318 | 1515768.691 | 265718.5299999999 | 1759429.2928 | 1763942.3265
GERMANY
 | Export | 1353855.3917 | 565108.4475000001 | 555136.9799999999 | 2474100.8192 | 2130582.225
 | Import | 1273200.6763 | 1088377.567 | 19050.07 | 2380628.3133 | 2118560.1515
 |  | 80654.7154000001 | 523269.1195 | 536086.9099999999 | 93472.50590000022 | 12022.07349999947
GREECE
 | Export |  |  | 40830.255 | 40830.255 | 46678.95300000001
 | Import | 0 | 0 | 136926.02 | 136926.02 | 136751.2605
 |  |  |  | 96095.765 | 96095.765 | 90072.3075
HONG KONG
 | Export |  | 22042912.129 | 1743382.325 | 23786294.454 | 24023757.9555
 | Import | 0 | 1642631.4445 | 131845.505 | 1774476.9495 | 1744182.426
 |  |  | 20400280.6845 | 1611536.82 | 22011817.5045 | 22279575.5295
HUNGARY
 | Export | 89743.54830000001 | 48440.38700000001 | 38523.50500000002 | 176707.4403 | 154882.6655
 | Import | 116534.2941 | 1613748.905 | 5343.43499999999 | 1735626.6341 | 1707265.0675
 |  | 26790.7458 | 1565308.518 | 33180.07000000002 | 1558919.1938 | 1552382.402
INDIA
 | Export |  |  | 2043803.385 | 2043803.385 | 3405603.2115
 | Import | 0 | 0 | 2882546.595 | 2882546.595 | 3029081.9665
 |  |  |  | 838743.210000001 | 838743.210000001 | 376521.2449999996
INDONESIA
 | Export |  | 1384841.094 | 45475.71500000003 | 1430316.809 | 1451393.8645
 | Import | 0 | 242070.655 | 10858.845 | 252929.5 | 250365.0765
 |  |  | 1142770.439 | 34616.87000000002 | 1177387.309 | 1201028.788
IRELAND
 | Export |  |  | 1034382.505 | 1034382.505 | 661734.8059999999
 | Import | 0 | 305914.9365 | 500658.665 | 806573.6015 | 630669.6635
 |  |  | 305914.9365 | 533723.8399999995 | 227808.9034999994 | 31065.14249999984
ITALY
 | Export | 563763.1678 | 342275.833 | 316728.8250000001 | 1222767.8258 | 1019398.808
 | Import | 425258.358 | 412412.4395 | 30301.97 | 867972.7675 | 752773.4245
 |  | 138504.8098 | 70136.6065000001 | 286426.855 | 354795.0583000001 | 266625.3835000001
JAPAN
 | Export |  | 2022936.3055 | 311947.095 | 2334883.4005 | 2297362.385
 | Import | 0 | 1398198.551 | 35527.2 | 1433725.751 | 1462552.12
 |  |  | 624737.7544999993 | 276419.895 | 901157.6494999996 | 834810.2649999992
JORDAN
 | Export |  |  | 262685.6800000003 | 262685.6800000003 | 261835.476
 | Import | 0 | 0 | 81944.79 | 81944.79 | 65255.5375
 |  |  |  | 180740.8900000004 | 180740.8900000004 | 196579.9385
[NAME] (CAMBODIA)
 | Export |  | 354222.4565 | 7508.919999999998 | 361731.3765 | 366432.877
 | Import | 0 | 12951.127 | 279.87 | 13230.997 | 13315.3575
 |  |  | 341271.3295000001 | 7229.049999999998 | 348500.3795 | 353117.5195000001
KOREA, REPUBLIC OF
 | Export |  | 2008216.033 | 166022.3250000001 | 2174238.358 | 2318454.815
 | Import | 0 | 1707137.2845 | 43444.935 | 1750582.2195 | 1774805.43
 |  |  | 301078.7485 | 122577.39 | 423656.1385000001 | 543649.3849999995
KUWAIT
 | Export |  |  | 3858.369999999999 | 3858.369999999999 | 4644.261
 | Import | 0 | 0 | 181245.745 | 181245.745 | 169083.0285
 |  |  |  | 177387.375 | 177387.375 | 164438.7675
LEBANON
 | Export |  |  | 33039.96999999999 | 33039.96999999999 | 29571.16
 | Import | 0 | 0 | 231168.125 | 231168.125 | 224589.336
 |  |  |  | 198128.155 | 198128.155 | 195018.176
MACAO (MACAU)
 | Export |  | 14660.9935 | 531.0749999999994 | 15192.0685 | 15657.834
 | Import | 0 | 8363.382 | 2.235 | 8365.617 | 8370.24
 |  |  | 6297.611499999999 | 528.8399999999993 | 6826.451499999997 | 7287.593999999999
MADAGASCAR
 | Export | 5253.0443 | 1921.2115 | 59.61000000000013 | 7233.865800000001 | 5786.4175
 | Import | 2846.6151 | 610.704 | 111.67 | 3568.9891 | 2886.726
 |  | 2406.4292 | 1310.5075 | 52.06 | 3664.876700000001 | 2899.6915
MALAYSIA
 | Export |  | 3735975.6155 | 127437.1199999999 | 3863412.735499999 | 3879442.123
 | Import | 0 | 1032332.5875 | 34195.02 | 1066527.6075 | 1061827.932
 |  |  | 2703643.028 | 93242.09999999987 | 2796885.128 | 2817614.191
MEXICO
 | Export |  | 1140822.4725 | 258006.98 | 1398829.4525 | 1376129.44
 | Import | 0 | 3569667.3695 | 181072.86 | 3750740.2295 | 3792290.5955
 |  |  | 2428844.897 | 76934.11999999991 | 2351910.777 | 2416161.1555
NETHERLANDS
 | Export | 493930.7184 | 296973.9280000001 | 375761.81 | 1166666.4564 | 1089816.3125
 | Import | 3243400.6101 | 5286563.7675 | 157284.29 | 8687248.6676 | 7778166.488
 |  | 2749469.8917 | 4989589.8395 | 218477.5199999999 | 7520582.2112 | 6688350.1755
NEW ZEALAND
 | Export |  |  | 111924.105 | 111924.105 | 110333.8035
 | Import | 0 | 0 | 343970.05 | 343970.05 | 313151.2515
 |  |  |  | 232045.945 | 232045.945 | 202817.448
NORWAY
 | Export | 72834.04660000002 | 18371.7915 | 14477.6 | 105683.4381 | 81326.441
 | Import | 104841.6873 | 36799.525 | 2544.24 | 144185.4523 | 115862.899
 |  | 32007.6407 | 18427.7335 | 11933.36 | 38502.0142 | 34536.458
OMAN
 | Export |  |  | 891.1550000000034 | 891.1550000000034 | 6201.675000000001
 | Import | 0 | 0 | 68321.435 | 68321.435 | 85829.5165
 |  |  |  | 67430.28 | 67430.28 | 79627.8415
PAKISTAN
 | Export |  |  | 64243.2450000002 | 64243.2450000002 | 410201.0555
 | Import | 0 | 0 | 66744.435 | 66744.435 | 126058.216
 |  |  |  | 2501.18999999981 | 2501.18999999981 | 284142.8395
PERU
 | Export |  |  | 210271.0949999999 | 210271.0949999999 | 131790.244
 | Import | 0 | 0 | 187070.57 | 187070.57 | 188525.507
 |  |  |  | 23200.52499999991 | 23200.52499999991 | 56735.263
PHILIPPINES
 | Export |  | 696814.9940000002 | 25408.75000000001 | 722223.7440000002 | 729547.8565000001
 | Import | 0 | 339423.501 | 9651.82 | 349075.321 | 352935.9015
 |  |  | 357391.4930000002 | 15756.93000000001 | 373148.4230000002 | 376611.9550000002
POLAND
 | Export | 141866.8139 | 34852.5655 | 15243.235 | 191962.6144 | 153945.6935
 | Import | 153780.8923 | 288972.2 | 40134.08 | 482887.1723 | 440652.5945
 |  | 11914.0784 | 254119.6345 | 24890.845 | 290924.5579 | 286706.901
PORTUGAL
 | Export | 80474.9799 | 5109.853499999999 | 12440.23000000001 | 98025.06340000001 | 83790.2445
 | Import | 23532.7218 | 69811.6595 | 11416.575 | 104760.9563 | 98732.484
 |  | 56942.25810000001 | 64701.806 | 1023.655000000006 | 6735.89289999996 | 14942.2395
PUERTO RICO
 | Export |  | 325944.963 | 54979.49499999999 | 380924.458 | 369120.7315
 | Import | 181279.7861 | 99164.0245 | 25194.035 | 305637.8456 | 266903.1035
 |  | 181279.7861 | 226780.9385 | 29785.45999999999 | 75286.6124000001 | 102217.628
QATAR
 | Export |  |  | 3772.365 | 3772.365 | 5000.212
 | Import | 0 | 0 | 235740.205 | 235740.205 | 226828.577
 |  |  |  | 231967.84 | 231967.84 | 221828.365
ROMANIA
 | Export | 30801.67300000001 | 5846.396500000001 | 10890.41000000001 | 47538.47950000001 | 34337.077
 | Import | 36524.0078 | 62825.6785 | 2570.65 | 101920.3363 | 93277.7095
 |  | 5722.33479999999 | 56979.282 | 8319.760000000007 | 54381.8568 | 58940.6325
SAUDI ARABIA
 | Export |  |  | 12389.02499999999 | 12389.02499999999 | 16870.814
 | Import | 0 | 0 | 927717.925 | 927717.925 | 1020375.5845
 |  |  |  | 915328.9 | 915328.9 | 1003504.7705
SINGAPORE
 | Export |  | 2200081.0035 | 375576.9349999999 | 2575657.9385 | 2586968.713
 | Import | 0 | 2123092.8285 | 90170.305 | 2213263.1335 | 2205744.881
 |  |  | 76988.17499999981 | 285406.6299999999 | 362394.8049999997 | 381223.8320000004
SOUTH AFRICA
 | Export |  | 0 | 181544.935 | 181544.935 | 268404.7825
 | Import | 0 | 587543.9885 | 633792.765 | 1221336.7535 | 1128385.155
 |  |  | 587543.9885 | 452247.83 | 1039791.8185 | 859980.3725
SPAIN
 | Export | 211184.925 | 261527.134 | 207331.2250000001 | 680043.2840000001 | 706775.1760000001
 | Import | 246489.7491 | 267258.203 | 44749.1099999999 | 558497.0621 | 557762.297
 |  | 35304.8241 | 5731.06900000011 | 162582.1150000002 | 121546.2219000001 | 149012.879
SRI LANKA
 | Export |  | 206291.548 | 28152.62 | 234444.168 | 221380.8965
 | Import | 0 | 34954.299 | 139.54 | 35093.839 | 35866.6625
 |  |  | 171337.249 | 28013.08 | 199350.329 | 185514.234
SWEDEN
 | Export | 242909.6152 | 146121.2925 | 57174.24000000002 | 446205.1477 | 392838.0860000001
 | Import | 143745.336 | 99636.32 | 18943.705 | 262325.361 | 234647.767
 |  | 99164.27919999999 | 46484.97250000002 | 38230.535 | 183879.7866999999 | 158190.3190000001
SWITZERLAND
 | Export | 158148.5283 | 99592.82950000002 | 71726.49999999997 | 329467.8578 | 286440.7265
 | Import | 188049.4833 | 62919.7755 | 7319.39 | 258288.6488 | 203533.701
 |  | 29900.955 | 36673.05400000002 | 64407.10999999997 | 71179.209 | 82907.02550000008
THAILAND
 | Export |  | 2260784.685000001 | 191242.5749999998 | 2452027.26 | 2546023.1335
 | Import | 0 | 581417.605 | 27467.795 | 608885.4 | 608150.4545
 |  |  | 1679367.080000001 | 163774.7799999998 | 1843141.86 | 1937872.679
TURKEY
 | Export |  |  | 257638.5549999998 | 257638.5549999998 | 366976.0485
 | Import | 0 | 0 | 737662.34 | 737662.34 | 769054.395
 |  |  |  | 480023.785 | 480023.785 | 402078.3465
UNITED ARAB EMIRATES
 | Export |  |  | 104701.645 | 104701.645 | 120080.807
 | Import | 0 | 0 | 1150135.565 | 1150135.565 | 1116336.834
 |  |  |  | 1045433.92 | 1045433.92 | 996256.027
UNITED KINGDOM
 | Export | 1078052.682 | 404850.759 | 479192.65 | 1962096.091 | 1575687.489
 | Import | 1544236.687 | 2481906.436 | 263424.935 | 4289568.058 | 3930714.873
 |  | 466184.005 | 2077055.677 | 215767.7150000001 | 2327471.967 | 2355027.384
UNITED STATES
 | Export | 9978013.648400003 | 8210702.524 | 9352202.715 | 27540918.88740001 | 23279384.567
 | Import | 5479689.3403 | 42828100.3475 | 3407594.36 | 51715384.0478 | 50796804.317
 |  | 4498324.308100003 | 34617397.8235 | 5944608.354999999 | 24174465.1604 | 27517419.75
VIETNAM
 | Export |  | 3527577.2805 | 59967.07500000003 | 3587544.355500001 | 3629274.7695
 | Import | 0 | 284116.667 | 19863.715 | 303980.382 | 310039.047
 |  |  | 3243460.6135 | 40103.36000000003 | 3283563.9735 | 3319235.7225