Gurus,
i am confused, and i do not know what i am really missing. i am putting the condition of sumproduct but it does return the correct value. it returns the total and the criteria filter does not work.
please take a look into the file uploaded in link https://skydrive.live.com/redir?resid=D7C00A2BF29043E0!282&authkey=!ACMnN7l5sNul-XM
Sheet1 is source of data and sheet2 is where i want to put my formula in columb B SUMPRODUCT to sum the sumrange of sheet1 based on the criteria to match the country.
appreciate your help.
sheet2
i am confused, and i do not know what i am really missing. i am putting the condition of sumproduct but it does return the correct value. it returns the total and the criteria filter does not work.
please take a look into the file uploaded in link https://skydrive.live.com/redir?resid=D7C00A2BF29043E0!282&authkey=!ACMnN7l5sNul-XM
Sheet1 is source of data and sheet2 is where i want to put my formula in columb B SUMPRODUCT to sum the sumrange of sheet1 based on the criteria to match the country.
appreciate your help.
| Excel 2010 | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |||
| 1 | Countries | 1997 | 1998 | 1999 | 2000 | 2001 | 2002 | 2003 | 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2010 | 2011 | 2012 | Totals | ||
| 2 | Abu Dhabi | 937526.00 | 711676.00 | 127258.00 | 412124.00 | 181343.00 | 148693.00 | 83602.00 | 705321.00 | 175918.00 | 27216.00 | 623127.00 | 413801.00 | 672402.00 | 92748.00 | 785860.00 | 518927.00 | 6617542.00 | ||
| 3 | Afghanistan | 544978.00 | 864802.00 | 328557.00 | 398491.00 | 151009.00 | 169373.00 | 570641.00 | 174884.00 | 773938.00 | 886205.00 | 189563.00 | 13662.00 | 376837.00 | 67268.00 | 745799.00 | 964773.00 | 7220780.00 | ||
| 4 | Albania | 722178.00 | 607479.00 | 409183.00 | 849599.00 | 986586.00 | 182140.00 | 605838.00 | 693561.00 | 877480.00 | 63517.00 | 213071.00 | 482273.00 | 355700.00 | 129737.00 | 847960.00 | 694915.00 | 8721217.00 | ||
| 5 | Algeria | 928072.00 | 951906.00 | 685024.00 | 595924.00 | 182293.00 | 407607.00 | 815725.00 | 48995.00 | 935532.00 | 581201.00 | 179045.00 | 221596.00 | 342495.00 | 510717.00 | 449213.00 | 884393.00 | 8719738.00 | ||
| 6 | American Samoa | 857344.00 | 814831.00 | 542987.00 | 580172.00 | 821140.00 | 498886.00 | 65486.00 | 24883.00 | 179550.00 | 940157.00 | 465544.00 | 737757.00 | 587136.00 | 87497.00 | 650218.00 | 994682.00 | 8848270.00 | ||
| 7 | Andorra | 355476.00 | 526425.00 | 428967.00 | 185387.00 | 112257.00 | 273832.00 | 236346.00 | 663817.00 | 724760.00 | 651659.00 | 29520.00 | 353506.00 | 626720.00 | 5168672.00 | |||||
| 8 | Angola | 180453.00 | 778205.00 | 557528.00 | 745210.00 | 254977.00 | 165896.00 | 417137.00 | 981202.00 | 112558.00 | 662935.00 | 944493.00 | 158502.00 | 557652.00 | 824050.00 | 451590.00 | 696412.00 | 8488800.00 | ||
| 9 | Anguilla | 965489.00 | 539975.00 | 481887.00 | 783462.00 | 443992.00 | 328277.00 | 291094.00 | 868261.00 | 252561.00 | 110590.00 | 208467.00 | 427815.00 | 134111.00 | 961677.00 | 752719.00 | 626397.00 | 8176774.00 | ||
| 10 | Antarctica | 746210.00 | 667204.00 | 499435.00 | 738105.00 | 737376.00 | 213987.00 | 531707.00 | 531162.00 | 674346.00 | 269192.00 | 538664.00 | 737290.00 | 48330.00 | 586768.00 | 727566.00 | 463294.00 | 8710636.00 | ||
| 11 | Antigua:Barbuda | 871815.00 | 667126.00 | 626736.00 | 734106.00 | 537175.00 | 547782.00 | 144702.00 | 26291.00 | 671698.00 | 91836.00 | 640484.00 | 59809.00 | 100967.00 | 362451.00 | 282105.00 | 992003.00 | 7357086.00 | ||
| 12 | Argentina | 302202.00 | 612481.00 | 70455.00 | 956737.00 | 353323.00 | 257251.00 | 8892.00 | 983000.00 | 378057.00 | 422215.00 | 933361.00 | 824528.00 | 517971.00 | 44786.00 | 554092.00 | 797717.00 | 8017068.00 | ||
| 13 | Armenia | 765227.00 | 177623.00 | 600643.00 | 183609.00 | 251758.00 | 161249.00 | 590328.00 | 554474.00 | 993286.00 | 634491.00 | 350483.00 | 531153.00 | 343998.00 | 352373.00 | 908402.00 | 98625.00 | 7497722.00 | ||
| 14 | Aruba | 308829.00 | 196117.00 | 232432.00 | 863837.00 | 961369.00 | 407216.00 | 280974.00 | 968869.00 | 753712.00 | 947885.00 | 453303.00 | 731810.00 | 273038.00 | 7379391.00 | |||||
| 15 | Austral Oceania | 293817.00 | 420002.00 | 420884.00 | 931570.00 | 173905.00 | 793280.00 | 36321.00 | 517644.00 | 958358.00 | 782425.00 | 466878.00 | 209224.00 | 890333.00 | 749790.00 | 886731.00 | 406176.00 | 8937338.00 | ||
| 16 | Australia | 320673.00 | 348176.00 | 272120.00 | 895560.00 | 756822.00 | 953593.00 | 816967.00 | 218470.00 | 332683.00 | 665997.00 | 277627.00 | 250978.00 | 648811.00 | 220082.00 | 603331.00 | 540796.00 | 8122686.00 | ||
| 17 | Austria | 917145.00 | 814099.00 | 232169.00 | 555706.00 | 820870.00 | 971075.00 | 660254.00 | 230117.00 | 639540.00 | 508702.00 | 69443.00 | 431686.00 | 963865.00 | 879475.00 | 758393.00 | 15686.00 | 9468225.00 | ||
| 18 | Azerbaijan | 965129.00 | 627219.00 | 187870.00 | 114325.00 | 576949.00 | 262992.00 | 638246.00 | 467506.00 | 739100.00 | 351699.00 | 597152.00 | 452789.00 | 18453.00 | 317744.00 | 982923.00 | 444441.00 | 7744537.00 | ||
| 19 | Bahamas | 786398.00 | 781888.00 | 746064.00 | 363161.00 | 928443.00 | 602711.00 | 9193.00 | 79837.00 | 799828.00 | 733154.00 | 498498.00 | 960061.00 | 132898.00 | 357905.00 | 770207.00 | 697289.00 | 9247535.00 | ||
| 20 | Bahrain | 220249.00 | 696354.00 | 362664.00 | 743280.00 | 923535.00 | 584035.00 | 741696.00 | 439483.00 | 917232.00 | 874141.00 | 817656.00 | 946701.00 | 24687.00 | 306877.00 | 293172.00 | 821157.00 | 9712919.00 | ||
| 21 | Bangladesh | 494660.00 | 872843.00 | 397344.00 | 327041.00 | 785574.00 | 23482.00 | 238176.00 | 786651.00 | 336705.00 | 642325.00 | 349725.00 | 847199.00 | 483065.00 | 634862.00 | 917144.00 | 402602.00 | 8539398.00 | ||
| 22 | Barbados | 743509.00 | 236583.00 | 973610.00 | 682360.00 | 733647.00 | 966871.00 | 928264.00 | 146517.00 | 485258.00 | 552076.00 | 831570.00 | 452946.00 | 7733211.00 | ||||||
| 23 | Belarus | 473324.00 | 640538.00 | 476508.00 | 580576.00 | 478323.00 | 723255.00 | 615947.00 | 154723.00 | 141933.00 | 274495.00 | 847180.00 | 731750.00 | 6138552.00 | ||||||
| 24 | Belgium | 31444.00 | 755835.00 | 182955.00 | 318377.00 | 753323.00 | 630496.00 | 251102.00 | 861636.00 | 451012.00 | 679682.00 | 481492.00 | 238586.00 | 5635940.00 | ||||||
| 25 | Belgium-Luxem. | 621043.00 | 154289.00 | 730073.00 | 937618.00 | 201640.00 | 806830.00 | 705419.00 | 466874.00 | 341075.00 | 563040.00 | 833703.00 | 33152.00 | 6394756.00 | ||||||
| 26 | Belize | 272482.00 | 302982.00 | 976806.00 | 421593.00 | 242862.00 | 319049.00 | 951608.00 | 238969.00 | 49153.00 | 511839.00 | 406030.00 | 265577.00 | 4958950.00 | ||||||
| 27 | Benin | 780060.00 | 558709.00 | 833655.00 | 721889.00 | 771842.00 | 126456.00 | 852886.00 | 956987.00 | 151708.00 | 77530.00 | 827289.00 | 181755.00 | 6840766.00 | ||||||
| 28 | Bermuda | 670778.00 | 470938.00 | 935723.00 | 432966.00 | 840120.00 | 25133.00 | 5922.00 | 825077.00 | 55776.00 | 124678.00 | 199117.00 | 127869.00 | 4714097.00 | ||||||
| 29 | Bhutan | 840703.00 | 621693.00 | 183970.00 | 324381.00 | 126021.00 | 581994.00 | 111329.00 | 804995.00 | 519017.00 | 304643.00 | 477513.00 | 163101.00 | 765638.00 | 187605.00 | 329273.00 | 193723.00 | 6535599.00 | ||
| 30 | Bolivia | 223536.00 | 554113.00 | 735413.00 | 710400.00 | 100886.00 | 15385.00 | 921030.00 | 825137.00 | 582626.00 | 420938.00 | 561120.00 | 531022.00 | 456317.00 | 312142.00 | 699126.00 | 11087.00 | 7660278.00 | ||
| 31 | Bonaire | 524639.00 | 959088.00 | 197217.00 | 786444.00 | 431270.00 | 60926.00 | 338509.00 | 682118.00 | 14374.00 | 270126.00 | 732451.00 | 879161.00 | 6903.00 | 373135.00 | 315900.00 | 904674.00 | 7476935.00 | ||
| 32 | Bosnia & Herz. | 904171.00 | 669850.00 | 926639.00 | 150594.00 | 686516.00 | 838620.00 | 128344.00 | 350546.00 | 596315.00 | 370644.00 | 520203.00 | 560840.00 | 320888.00 | 403504.00 | 167765.00 | 399856.00 | 7995295.00 | ||
| 33 | Botswana | 499262.00 | 572952.00 | 33571.00 | 947266.00 | 151588.00 | 352496.00 | 44132.00 | 268118.00 | 603600.00 | 991227.00 | 266802.00 | 71502.00 | 528354.00 | 776880.00 | 719881.00 | 312401.00 | 7140032.00 | ||
| 34 | Bouvet Island | 799794.00 | 398349.00 | 854222.00 | 761757.00 | 7595.00 | 450894.00 | 69092.00 | 136505.00 | 469986.00 | 125629.00 | 370652.00 | 91239.00 | 259313.00 | 392166.00 | 964373.00 | 674124.00 | 6825690.00 | ||
| 35 | Br Ind Oc Terr | 735262.00 | 754270.00 | 394101.00 | 438565.00 | 941353.00 | 408716.00 | 755872.00 | 807967.00 | 45685.00 | 977331.00 | 689007.00 | 29785.00 | 938033.00 | 691868.00 | 455479.00 | 228332.00 | 9291626.00 | ||
| 36 | Br Virgin Is | 346770.00 | 638654.00 | 44270.00 | 208166.00 | 205256.00 | 145644.00 | 737296.00 | 35990.00 | 111447.00 | 670231.00 | 724311.00 | 920428.00 | 481593.00 | 668509.00 | 252482.00 | 93548.00 | 6284595.00 | ||
| 37 | Brazil | 976186.00 | 550515.00 | 31598.00 | 179050.00 | 36502.00 | 152528.00 | 126635.00 | 219462.00 | 34528.00 | 321369.00 | 604021.00 | 430329.00 | 660585.00 | 875030.00 | 374718.00 | 340483.00 | 5913539.00 | ||
| 38 | Brunei | 371630.00 | 492992.00 | 267577.00 | 265543.00 | 704446.00 | 248897.00 | 977087.00 | 918780.00 | 517135.00 | 842921.00 | 302525.00 | 942834.00 | 96297.00 | 505730.00 | 565444.00 | 489457.00 | 8509295.00 | ||
| 39 | Bulgaria | 489854.00 | 158988.00 | 249951.00 | 775585.00 | 917596.00 | 465874.00 | 740503.00 | 625140.00 | 597253.00 | 944977.00 | 331276.00 | 6296997.00 | |||||||
| 40 | Burkina | 97446.00 | 822169.00 | 240510.00 | 23310.00 | 91890.00 | 355228.00 | 196201.00 | 302240.00 | 279736.00 | 220839.00 | 666268.00 | 3295837.00 | |||||||
| 41 | Burma | 661.00 | 562744.00 | 970128.00 | 214630.00 | 332825.00 | 166796.00 | 492384.00 | 434506.00 | 998775.00 | 228859.00 | 941775.00 | 5344083.00 | |||||||
| 42 | Burundi | 726298.00 | 780712.00 | 750963.00 | 807801.00 | 977219.00 | 513221.00 | 323043.00 | 218567.00 | 214809.00 | 5312633.00 | |||||||||
| 43 | Cambodia | 210209.00 | 676742.00 | 682897.00 | 69089.00 | 372757.00 | 460786.00 | 753663.00 | 15159.00 | 627889.00 | 3869191.00 | |||||||||
| 44 | Cameroon | 956564.00 | 165906.00 | 934386.00 | 6542.00 | 879015.00 | 576356.00 | 737480.00 | 366965.00 | 207948.00 | 4831162.00 | |||||||||
| 45 | Canada | 67282.00 | 529690.00 | 216369.00 | 300016.00 | 265453.00 | 865015.00 | 713846.00 | 831332.00 | 787154.00 | 4576157.00 | |||||||||
| 46 | Canary Islands | 346197.00 | 632205.00 | 584615.00 | 542025.00 | 482072.00 | 4696.00 | 30833.00 | 1021.00 | 326099.00 | 2949763.00 | |||||||||
| 47 | Cape Verde | 766789.00 | 753738.00 | 102898.00 | 286404.00 | 657922.00 | 406033.00 | 734510.00 | 183834.00 | 106841.00 | 3998969.00 | |||||||||
| 48 | Cayman Islands | 401281.00 | 854459.00 | 918881.00 | 948431.00 | 548604.00 | 206654.00 | 660730.00 | 723741.00 | 159660.00 | 122490.00 | 608318.00 | 182202.00 | 800488.00 | 900942.00 | 8036881.00 | ||||
| 49 | Cent Afr Rep | 351701.00 | 857309.00 | 876121.00 | 235583.00 | 389388.00 | 13879.00 | 480054.00 | 985453.00 | 344428.00 | 36547.00 | 841194.00 | 754651.00 | 198660.00 | 659810.00 | 7024778.00 | ||||
| 50 | 334281941.00 | |||||||||||||||||||
Sheet1 | ||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| R2 | =SUM(B2:Q2) | |
| R3 | =SUM(B3:Q3) | |
| R4 | =SUM(B4:Q4) | |
| R5 | =SUM(B5:Q5) | |
| R6 | =SUM(B6:Q6) | |
| R7 | =SUM(B7:Q7) | |
| R8 | =SUM(B8:Q8) | |
| R9 | =SUM(B9:Q9) | |
| R10 | =SUM(B10:Q10) | |
| R11 | =SUM(B11:Q11) | |
| R12 | =SUM(B12:Q12) | |
| R13 | =SUM(B13:Q13) | |
| R14 | =SUM(B14:Q14) | |
| R15 | =SUM(B15:Q15) | |
| R16 | =SUM(B16:Q16) | |
| R17 | =SUM(B17:Q17) | |
| R18 | =SUM(B18:Q18) | |
| R19 | =SUM(B19:Q19) | |
| R20 | =SUM(B20:Q20) | |
| R21 | =SUM(B21:Q21) | |
| R22 | =SUM(B22:Q22) | |
| R23 | =SUM(B23:Q23) | |
| R24 | =SUM(B24:Q24) | |
| R25 | =SUM(B25:Q25) | |
| R26 | =SUM(B26:Q26) | |
| R27 | =SUM(B27:Q27) | |
| R28 | =SUM(B28:Q28) | |
| R29 | =SUM(B29:Q29) | |
| R30 | =SUM(B30:Q30) | |
| R31 | =SUM(B31:Q31) | |
| R32 | =SUM(B32:Q32) | |
| R33 | =SUM(B33:Q33) | |
| R34 | =SUM(B34:Q34) | |
| R35 | =SUM(B35:Q35) | |
| R36 | =SUM(B36:Q36) | |
| R37 | =SUM(B37:Q37) | |
| R38 | =SUM(B38:Q38) | |
| R39 | =SUM(B39:Q39) | |
| R40 | =SUM(B40:Q40) | |
| R41 | =SUM(B41:Q41) | |
| R42 | =SUM(B42:Q42) | |
| R43 | =SUM(B43:Q43) | |
| R44 | =SUM(B44:Q44) | |
| R45 | =SUM(B45:Q45) | |
| R46 | =SUM(B46:Q46) | |
| R47 | =SUM(B47:Q47) | |
| R48 | =SUM(B48:Q48) | |
| R49 | =SUM(B49:Q49) | |
| R50 | =SUM(R2:R49) | |
sheet2
| Excel 2010 | ||||
|---|---|---|---|---|
| A | B | |||
| 1 | Countries | Sum of Total Years with SUMPRODUCT | ||
| 2 | Abu Dhabi | 334281941.00 | ||
| 3 | Afghanistan | |||
| 4 | Albania | |||
| 5 | Algeria | |||
| 6 | American Samoa | |||
| 7 | Andorra | |||
| 8 | Angola | |||
| 9 | Anguilla | |||
| 10 | Antarctica | |||
| 11 | Antigua:Barbuda | |||
| 12 | Argentina | |||
| 13 | Armenia | |||
| 14 | Aruba | |||
| 15 | Austral Oceania | |||
| 16 | Australia | |||
| 17 | Austria | |||
| 18 | Azerbaijan | |||
| 19 | Bahamas | |||
| 20 | Bahrain | |||
| 21 | Bangladesh | |||
| 22 | Barbados | |||
| 23 | Belarus | |||
| 24 | Belgium | |||
| 25 | Belgium-Luxem. | |||
| 26 | Belize | |||
| 27 | Benin | |||
| 28 | Bermuda | |||
| 29 | Bhutan | |||
| 30 | Bolivia | |||
| 31 | Bonaire | |||
| 32 | Bosnia & Herz. | |||
| 33 | Botswana | |||
| 34 | Bouvet Island | |||
| 35 | Br Ind Oc Terr | |||
| 36 | Br Virgin Is | |||
| 37 | Brazil | |||
Sheet2 | ||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2 | =SUMPRODUCT(SUMRANGE)*--ISNUMBER(MATCH(A2,DynamicRange,0)) | |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| DynamicRange | =Sheet1!$A$1:INDEX(Sheet1!$A:$A,Lastrow) | |
| SUMRANGE | =Sheet1!$B$2:$Q$49 | |
