Spreadsheet: 111743d1308062080-excel-2007-lookup-vlookup-or-if-commission-test
Sheet: May Commissoin

SALES REPORT - COMMISSIONABLE ACCOUNTS - MAY 2011 (PER 3)
Cust # | Reg | [NAME] | Rep | Line | Sales | Charges | Net Sales | Comm Rate | Comm Pd. | [NAME]
103058 | 27 | 60 | 30 | GAD | 5201.2 | -116.2 | 5085 | 5 |  | Look at column G.  If it contains any of the numbers below, the revised commission is 0, else pay the amount in col J.
103060 | 27 | 59 | 7 | GAD | 500.28 | 0 | 500.28 | 5
103085 | 27 | 59 | 77 | GAD | 186 | 11.78 | 197.78 | 5 |  |  | Reps that do not get paid commission
103516 | 72 | 61 | 812 | GAD | 513 | 91.15 | 604.15 | 5 |  |  | 19,27,34,55,59,60,61,72,73,90,91,92,93
103555 | 27 | 55 | 4C | CIA | 19608.3 | 780.5 | 20388.8 | 8
103555 | 27 | 55 | 101 | DW | 2274 | 0 | 2274 | 5
103555 | 27 | 55 | 101 | FW | 94376.84 | 3123.45 | 97500.29 | 5
103555 | 27 | 55 | 76 | GAD | 8317.5 | -6536.53 | 1780.97 | 5
103565 | 27 | 59 | 33 | GAD | 7274.1 | 0 | 7274.1 | 5
103652 | 27 | 59 | 66 | GAD | 273 | 0 | 273 | 5
103676 | 72 | 61 | 812 | GAD | 490.2 | 0 | 490.2 | 5
103701 | 27 | 55 | 55 | GAD | 112.5 | 7.08 | 119.58 | 0
103736 | 72 | 91 | 79 | FW | 9780 | 0 | 9780 | 3.5
103736 | 72 | 91 | 79 | GAD | 50502.71 | 0 | 50502.71 | 5
103737 | 72 | 91 | 79 | FW | 1998.5 | 0 | 1998.5 | 3.5
104099 | 27 | 60 | 60 | GAD | 900 | 0 | 900 | 0
104102 | 27 | 59 | 102 | FW | 642.25 | 0 | 642.25 | 3.5
104137 | 72 | 61 | 811 | GAD | 378.2 | 0 | 378.2 | 10
104368 | 27 | 59 | 59 | GAD | 1083.6 | 0 | 1083.6 | 0
104427 | 27 | 55 | 15 | GAD | 749.82 | 0 | 749.82 | 5
104500 | 27 | 55 | 76 | GAD | 6815.52 | -800.8299999999999 | 6014.690000000001 | 5
104572 | 72 | 61 | 812 | GAD | 857.6 | 0 | 857.6 | 10
104607 | 72 | 92 | 102 | FW | 34 | 0 | 34 | 3.5
104607 | 72 | 92 | 102 | GAD | 6809.34 | 0 | 6809.34 | 3.5
104711 | 27 | 27 | 102 | FW | 32520 | 0 | 32520 | 3.5
104711 | 27 | 27 | 102 | GAD | 3068.98 | -8313.41 | -5244.43 | 5
104713 | 27 | 59 | 77 | GAD | 528 | 0 | 528 | 5
104855 | 27 | 55 | 55 | GAD | 28980 | 0 | 28980 | 2
105076 | 27 | 59 | 102 | GAD | 2915.52 | 0 | 2915.52 | 5
105103 | 72 | 61 | 61 | DW | 5616 | 0 | 5616 | 0
105103 | 72 | 61 | 61 | FW | 3840 | 0 | 3840 | 0
105103 | 72 | 61 | 61 | GAD | 1548 | 0 | 1548 | 0
105196 | 27 | 59 | 77 | GAD | 405 | -12 | 393 | 5
105278 | 27 | 55 | 55 | GAD | 278.4 | 16.02 | 294.42 | 0
105523 | 27 | 55 | 55 | CIA | 1581.2 | -44.6 | 1536.6 | 8
105532 | 27 | 55 | 12 | GAD | 252 | 51.24 | 303.24 | 10
105561 | 72 | 61 | 61 | GAD | 16162.08 | 0 | 16162.08 | 5
105594 | 27 | 60 | 60 | GAD | 333 | -47.19 | 285.81 | 0
105697 | 27 | 59 | 6C | CIA | 5545.5 | 0 | 5545.5 | 8
106048 | 72 | 61 | 61 | GAD | 1413.6 | 0 | 1413.6 | 0
106079 | 72 | 72 | 3 | GAD | 397877.76 | -89648.86 | 308228.9 | 1
106159 | 27 | 59 | 77 | GAD | 965.04 | 0 | 965.04 | 5
106304 | 27 | 59 | 59 | GAD | 42 | 7.44 | 49.44 | 5
106575 | 27 | 60 | 801 | FW | 316.8 | 0 | 316.8 | 5
107351 | 72 | 61 | 15 | GAD | 81452.72 | -6064.53 | 75388.19 | 5
212501 | 27 | 55 | 57 | GAD | 51634.8 | -666.16 | 50968.64 | 5
212502 | 27 | 55 | 57 | GAD | 10406.62 | -2.4 | 10404.22 | 5
217891 | 27 | 61 | 48 | GAD | 1128.3 | 0 | 1128.3 | 5
233860 | 72 | 61 | 61 | GAD | 624 | 0 | 624 | 10
236233 | 72 | 61 | 61 | GAD | 3307.2 | 0 | 3307.2 | 0
236234 | 72 | 72 | 10C | CIA | 822 | 0 | 822 | 8
236237 | 27 | 55 | 55 | GAD | 655.2 | 0 | 655.2 | 0
238902 | 27 | 55 | 55 | GAD | 3024.06 | 0 | 3024.06 | 5
239201 | 27 | 59 | 77 | GAD | 1416 | 0 | 1416 | 5
242010 | 27 | 59 | 59 | CIA | 244 | 0 | 244 | 10
242010 | 72 | 61 | 812 | GAD | 61.9 | 0 | 61.9 | 10
244806 | 27 | 59 | 59 | GAD | 502.68 | 0 | 502.68 | 5
244807 | 27 | 59 | 59 | GAD | 303.48 | 0 | 303.48 | 5
244810 | 27 | 59 | 59 | GAD | 232.26 | 0 | 232.26 | 5
244811 | 27 | 59 | 59 | GAD | 354.96 | 0 | 354.96 | 5
244835 | 27 | 59 | 59 | GAD | 273.9 | 0 | 273.9 | 5
244869 | 27 | 59 | 59 | GAD | 325.74 | 0 | 325.74 | 5
245602 | 27 | 59 | 77 | GAD | 5301.6 | 0 | 5301.6 | 5
245650 | 27 | 55 | 55 | GAD | 3556.8 | -339.69 | 3217.11 | 5
255549 | 27 | 55 | 57 | GAD | 4310.4 | 0 | 4310.4 | 5
264501 | 27 | 55 | 12 | GAD | 458.5 | 0 | 458.5 | 5
267851 | 72 | 61 | 61 | GAD | 333 | 16.75 | 349.75 | 0
278350 | 27 | 59 | 59 | GAD | 1006.2 | 0 | 1006.2 | 0
285700 | 27 | 59 | 77 | GAD | 1028.24 | -40.32 | 987.92 | 5
286499 | 27 | 59 | 77 | GAD | 1512 | -34.9 | 1477.1 | 5
292840 | 27 | 59 | 77 | FW | 882 | 0 | 882 | 5
292840 | 27 | 59 | 77 | GAD | 12910.96 | 0 | 12910.96 | 5
296510 | 27 | 55 | 12 | GAD | 294.84 | 17.08 | 311.92 | 5
301875 | 27 | 59 | 77 | GAD | 826.8 | 57.31 | 884.1099999999999 | 5
307780 | 72 | 61 | 61 | GAD | 3119.4 | 0 | 3119.4 | 10
308150 | 72 | 61 | 811 | GAD | 3287.9 | 0 | 3287.9 | 10
309703 | 27 | 27 | 102 | FW | 4701 | 0 | 4701 | 3.5
309703 | 72 | 72 | 79 | GAD | 7030.15 | 0 | 7030.15 | 5
309715 | 72 | 73 | 35 | GAD | 16800.48 | -572.88 | 16227.6 | 4
309805 | 27 | 59 | 59 | GAD | 104.4 | 32.88 | 137.28 | 0
309812 | 27 | 59 | 59 | GAD | 196.1 | 32.03 | 228.13 | 5
309818 | 27 | 59 | 59 | GAD | 192.48 | 23.94 | 216.42 | 0
317650 | 27 | 60 | 60 | FW | 4470 | -83.4 | 4386.6 | 3
319551 | 27 | 59 | 59 | GAD | 1676.4 | -31.11 | 1645.29 | 5
322649 | 72 | 61 | 811 | GAD | 1250.88 | 0 | 1250.88 | 10
322650 | 27 | 60 | 60 | GAD | 1150.92 | -184.56 | 966.3600000000001 | 0
322659 | 27 | 59 | 6C | CIA | 294 | 0 | 294 | 10
331026 | 27 | 59 | 6C | CIA | 61134.05 | 0 | 61134.05 | 10
342287 | 27 | 27 | 102 | DW | 2093.36 | 0 | 2093.36 | 3.5
342287 | 27 | 27 | 102 | FW | 14142 | 0 | 14142 | 3.5
342287 | 27 | 27 | 102 | GAD | 7440.75 | 0 | 7440.75 | 5
342300 | 27 | 27 | 102 | DW | 1445.02 | 0 | 1445.02 | 3.5
342300 | 27 | 27 | 102 | FW | 8382 | 0 | 8382 | 3.5
342300 | 27 | 27 | 102 | GAD | 5477.85 | 0 | 5477.85 | 5
347204 | 72 | 72 | 79 | CIA | 10.5 | 0 | 10.5 | 5
347204 | 72 | 72 | 79 | FW | 180 | 0 | 180 | 5
347204 | 72 | 72 | 79 | GAD | 4118.45 | -13.5 | 4104.95 | 5
351450 | 72 | 61 | 48 | GAD | 28295.68 | 0 | 28295.68 | 5
351454 | 27 | 55 | 101 | FW | 35383 | 0 | 35383 | 5
351454 | 27 | 55 | 101 | GAD | 2184.2 | 0 | 2184.2 | 5
356100 | 27 | 55 | 15 | GAD | 17151.27 | -4377.110000000001 | 12774.16 | 5
356992 | 27 | 59 | 77 | GAD | 1225.5 | 38.13 | 1263.63 | 5
367004 | 72 | 61 | 813 | GAD | 12268.8 | -140.25 | 12128.55 | 5
373801 | 27 | 55 | 55 | DW | 24960 | 0 | 24960 | 5
373801 | 27 | 55 | 55 | GAD | 20736 | 0 | 20736 | 5
374004 | 72 | 73 | 35 | GAD | 8023.44 | 0 | 8023.44 | 5
376550 | 27 | 60 | 89 | GAD | 4992 | 0 | 4992 | 10
382005 | 27 | 59 | 77 | GAD | 497.9 | 0 | 497.9 | 5
382007 | 27 | 59 | 77 | GAD | 2916 | 0 | 2916 | 5
382009 | 27 | 59 | 77 | GAD | 1705.8 | -848.4 | 857.4 | 5
382011 | 27 | 59 | 77 | GAD | 663.9 | 0 | 663.9 | 5
384300 | 27 | 59 | 102 | GAD | 9789 | 0 | 9789 | 3
384430 | 27 | 59 | 7 | GAD | 5322.96 | 0 | 5322.96 | 5
386705 | 27 | 60 | 89 | GAD | 23508 | -110.5 | 23397.5 | 10
393900 | 27 | 27 | 102 | GAD | 70284.3 | -8318.62 | 61965.68 | 3
401501 | 27 | 59 | 77 | GAD | 1326.4 | -103 | 1223.4 | 5
401520 | 27 | 59 | 59 | GAD | 1108.8 | 0 | 1108.8 | 0
409203 | 27 | 59 | 59 | GAD | 1276.8 | -5.24 | 1271.56 | 0
409300 | 27 | 60 | 60 | GAD | 2379.84 | -196.77 | 2183.07 | 0
412200 | 27 | 59 | 66 | GAD | 10146 | -1190.1 | 8955.9 | 5
412201 | 27 | 59 | 77 | GAD | 1792 | 0 | 1792 | 5
419000 | 27 | 55 | 55 | GAD | 670.5 | 0 | 670.5 | 5
421957 | 27 | 55 | 101 | FW | 8291.95 | 0 | 8291.95 | 5
429811 | 27 | 59 | 812 | CIA | 519.8 | 0 | 519.8 | 10
429811 | 27 | 59 | 812 | GAD | 3002.1 | 0 | 3002.1 | 10
431400 | 27 | 55 | 55 | GAD | 1117.45 | 0 | 1117.45 | 5
433299 | 27 | 55 | 55 | GAD | 180 | 5.97 | 185.97 | 5
434001 | 27 | 59 | 59 | GAD | 262.5 | 10.96 | 273.46 | 0
434300 | 27 | 60 | 801 | FW | 4528.56 | 0 | 4528.56 | 5
434300 | 27 | 60 | 801 | GAD | 5853.96 | -360 | 5493.96 | 5
440004 | 72 | 1 | 1 | GAD | 243.39 | 68.57 | 311.96 | 0
459007 | 27 | 60 | 60 | FW | 21127.11 | 0 | 21127.11 | 0
465000 | 27 | 55 | 55 | GAD | 12684.48 | -327.75 | 12356.73 | 5
469990 | 27 | 34 | 34 | GAD | 5158.44 | -1587.03 | 3571.41 | 0
475805 | 27 | 59 | 59 | GAD | 1350 | 0 | 1350 | 0
481365 | 27 | 55 | 15 | GAD | 7750.98 | 0 | 7750.98 | 5
487600 | 27 | 90 | 90 | DW | 34539.9 | -3943.25 | 30596.65 | 0
487600 | 27 | 90 | 90 | FW | 640661.8 | -9944.650000000001 | 630717.15 | 0
487600 | 27 | 90 | 90 | GAD | 1726579.39 | -114027.87 | 1612551.52 | 0
488500 | 72 | 73 | 35 | GAD | 1468.8 | 0 | 1468.8 | 5
500000 | 72 | 61 | 48 | GAD | 2459.76 | 0 | 2459.76 | 10
503100 | 72 | 61 | 48 | GAD | 6819.12 | 0 | 6819.12 | 5
800001 | 27 | 59 | 41 | GAD | 220.8 | -20.59 | 200.21 | 10
900901 | 27 | 55 | 15 | DW | 1566 | 0 | 1566 | 5
905443 | 27 | 59 | 77 | GAD | 1606.8 | -3419.58 | -1812.78 | 5
905627 | 27 | 55 | 55 | GAD | 9181.2 | 0 | 9181.2 | 10
905636 | 27 | 55 | 55 | GAD | 600 | 0 | 600 | 0
907002 | 27 | 60 | 60 | GAD | 324 | 0 | 324 | 0
C103631 | 27 | 55 | 4C | CIA | 280.35 | 0 | 280.35 | 8
C103631 | 27 | 55 | 55 | GAD | 35 | 9.09 | 44.09 | 8
C104363 | 27 | 59 | 6C | CIA | 400 | 0 | 400 | 8
[NAME] | 27 | 55 | 10C | CIA | 2576.4 | 0 | 2576.4 | 8
C105197 | 27 | 59 | 6C | CIA | 2702 | -170.28 | 2531.72 | 8
C105538 | 72 | 61 | 811 | CIA | 673.6 | 0 | 673.6 | 10
C105574 | 27 | 55 | 4C | CIA | 126.4 | 0 | 126.4 | 8
C105692 | 27 | 55 | 55 | CIA | 1967.5 | 0 | 1967.5 | 8
C105693 | 27 | 59 | 59 | CIA | 35932 | 0 | 35932 | 0
C242006 | 27 | 55 | 4C | CIA | 126.7 | 0 | 126.7 | 8
C347220 | 27 | 59 | 59 | CIA | 270.55 | 0 | 270.55 | 8
C351451 | 72 | 61 | 48C | CIA | 1896.6 | 0 | 1896.6 | 10
C430102 | 27 | 59 | 59 | CIA | 1233 | 0 | 1233 | 0
[NAME] | 27 | 59 | 6C | CIA | 240 | 0 | 240 | 8
C905459 | 27 | 55 | 55 | CIA | 1988.4 | 0 | 1988.4 | 8
C905479 | 27 | 55 | 55 | CIA | 149.5 | 0 | 149.5 | 8
C905486 | 27 | 55 | 4C | CIA | 1435 | 0 | 1435 | 8
C905502 | 27 | 59 | 6C | CIA | 1736.5 | 0 | 1736.5 | 8
C905504 | 27 | 59 | 59 | CIA | 24 | 0 | 24 | 8
C905524 | 27 | 59 | 6C | CIA | 361.55 | -138.4 | 223.15 | 8
C905544 | 27 | 59 | 6C | CIA | 23290 | 0 | 23290 | 8
C905547 | 27 | 55 | 4C | CIA | 734.65 | 0 | 734.65 | 8
C905549 | 27 | 59 | 6C | CIA | 544.1 | -18.57 | 525.53 | 8
C905582 | 27 | 59 | 12C | CIA | 527 | 0 | 527 | 8
C905594 | 27 | 55 | 55 | CIA | 107.75 | 0 | 107.75 | 8
C905600 | 27 | 55 | 4C | CIA | 16.5 | -457.35 | -440.85 | 8
C905605 | 27 | 55 | 55 | CIA | 551.85 | 0 | 551.85 | 8
C905616 | 27 | 55 | 55 | CIA | 643.1 | 0 | 643.1 | 8
C905623 | 27 | 59 | 6C | CIA | 1180.2 | 0 | 1180.2 | 8
C905654 | 27 | 59 | 59 | CIA | 411 | 0 | 411 | 10
C905657 | 27 | 55 | 55 | CIA | 556.5 | 0 | 556.5 | 8
C905661 | 27 | 55 | 4C | CIA | 499.35 | 0 | 499.35 | 8
C905672 | 72 | 61 | 48C | CIA | 2355.4 | 0 | 2355.4 | 10
C905685 | 27 | 59 | 59 | CIA | 394.25 | 0 | 394.25 | 8
C905695 | 27 | 59 | 59 | CIA | 174 | 0 | 174 | 0
C905707 | 27 | 55 | 55 | CIA | 450 | 0 | 450 | 8
C905719 | 27 | 55 | 4C | CIA | 2637.8 | 0 | 2637.8 | 8
C905723 | 27 | 55 | 4C | CIA | 1649.9 | 0 | 1649.9 | 8
 |  |  |  |  | 3939341.37 | -263284.03 | 3676057.34
 |  |  |  |  | 3939341.37 | -263284.0299999999 | 3676057.34