Urgent SUMPRODUCT Does not work.

[jamilm]

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.

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

 

[jamilm]

Thank you very much Aladin. I would say you are indeed the King of Formulas.

I have never seen a person knowledgable like you to this extend in excel functions.

Everybody is happily producing things like X*--(Y). This is something incoherent, not worth to be wedded to.

If you don't have any formulas in column A on Sheet1...

Define Lrow with Sheet1 as Scope as referring to:

=MATCH(REPT("z",255),Sheet1!$A:$A)

Define ConditionRange as referring to:

=Sheet1!$A$1:INDEX(Sheet1!$A:$A,Sheet1!Lrow)

Define SumRange as referring to:

=Sheet1!$B$1:INDEX(Sheet1!$Q:$Q,Sheet1!Lrow)


Note 1. Restricting the scope of Lrow to Sheet1, allows you to use the same name also in other sheets.

Note 2. ConditionRange and SumRange are equally sized in the vertical dimension.

Note 3. With the dynamic name ranges we just defined, we can invoke formulas on Sheet2 like:

[1] just enter:

=SUMPRODUCT(SumRange*(ConditionRange=$A2))

[2] just enter:

=SUMPRODUCT(SumRange*ISNUMBER(MATCH(ConditionRange,$A2,0)))

[3] just enter:

=SUMPRODUCT(SumRange*ISNUMBER(SEARCH($A2,ConditionRange)))

[4] control+shift+enter, not just enter:

=SUM(IF(ConditionRange=$A2,SumRange)

[5] control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(MATCH(ConditionRange,$A2,0)),SumRange)

[6] control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(SEARCH($A2,ConditionRange)),SumRange)

Note 3. The following formula, similar to the one you have,

SUMPRODUCT(SumRange*ISNUMBER(MATCH($A2,ConditionRange,0)))

is wrong for MATCH here cannot but return a single value, not a set of values as required.

Note 4. The SumProduct formulas, [1] to [3] above, are sensitive to the text values (like blanks) in SumRange. Better to switch to a form like in 4 to 6 whenever you have to mix vectors with matrices.

 

[Aladin Akyurek]

I'm glad you read this thread and I was going to mention that there would be better options.

Thank you very much Aladin. I would say you are indeed the King of Formulas.

I have never seen a person knowledgable like you to this extend in excel functions.

Happy to contribute on such issues. Thanks both for the feedback.

 

[jamilm]

Dear Aladin,

i have one challenging difficult problem with Formula. not sure what combination of functions it requires to produce the desired result

Sheet1 one is source data where the column headers are dates like this format for example 01/31/2013 shown as Jan-2013 mmm-yyyy
Now Sheet2 is where, the numbers needs to be pulled from sheet1 and summed in Sheet2. but the main problem is that the column headers in sheet2 are not dates but they are texts with multi months. for example one column has Jan-Mar 2013. so is there any possibility for any formula to match country name and then the column header of multi months of sheet2 with column months of sheet1 and calculate the matched one. for example in second row for Abu Dhabi sheet2 , it matches with Sheet1 second row and then it should look in column headers of sheet2 which indicates multiple months . so for example amounts in sheet1 Jan, Feb and March to be summed in Sheet2 Column B Abu Dhabi which is row 2. becuase the header of sheet2 is Jan-Mar 2013

the example file is available in this link https://skydrive.live.com/redir?resid=D7C00A2BF29043E0!283&authkey=!AGT3V33Wt9HWs60 also the screenshots are attached.

I greatly appreciate your help as usual. thank you.

Sheet1 Data

[TABLE="width: 694"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Countries[/TD]
[TD]Jan-13[/TD]
[TD]Feb-13[/TD]
[TD]Mar-13[/TD]
[TD]Apr-13[/TD]
[TD]May-13[/TD]
[TD]Jun-13[/TD]
[TD]Jul-13[/TD]
[TD]Aug-13[/TD]
[TD]Sep-13[/TD]
[TD]Oct-13[/TD]
[TD]Nov-13[/TD]
[TD]Dec-13[/TD]
[/TR]
[TR]
[TD]Abu Dhabi[/TD]
[TD="align: right"]2852[/TD]
[TD="align: right"]194[/TD]
[TD="align: right"]334[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]197[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]214[/TD]
[TD="align: right"]173[/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Afghanistan[/TD]
[TD="align: right"]2144[/TD]
[TD="align: right"]487[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]482[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]405[/TD]
[TD="align: right"]244[/TD]
[TD="align: right"]390[/TD]
[TD="align: right"]305[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]443[/TD]
[TD="align: right"]237[/TD]
[/TR]
[TR]
[TD]Albania[/TD]
[TD="align: right"]689[/TD]
[TD="align: right"]236[/TD]
[TD="align: right"]176[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]303[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]254[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]398[/TD]
[TD="align: right"]367[/TD]
[TD="align: right"]271[/TD]
[/TR]
[TR]
[TD]Algeria[/TD]
[TD="align: right"]2028[/TD]
[TD="align: right"]239[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]114[/TD]
[TD="align: right"]114[/TD]
[TD="align: right"]243[/TD]
[TD="align: right"]447[/TD]
[TD="align: right"]124[/TD]
[TD="align: right"]359[/TD]
[TD="align: right"]194[/TD]
[TD="align: right"]310[/TD]
[TD="align: right"]58[/TD]
[/TR]
[TR]
[TD]American Samoa[/TD]
[TD="align: right"]895[/TD]
[TD="align: right"]492[/TD]
[TD="align: right"]414[/TD]
[TD="align: right"]453[/TD]
[TD="align: right"]376[/TD]
[TD="align: right"]177[/TD]
[TD="align: right"]218[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]254[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]444[/TD]
[/TR]
[TR]
[TD]Andorra[/TD]
[TD="align: right"]365[/TD]
[TD="align: right"]254[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]376[/TD]
[TD="align: right"]442[/TD]
[TD="align: right"]251[/TD]
[TD="align: right"]492[/TD]
[TD="align: right"]499[/TD]
[TD="align: right"]416[/TD]
[TD="align: right"]340[/TD]
[TD="align: right"]231[/TD]
[/TR]
[TR]
[TD]Angola[/TD]
[TD="align: right"]1135[/TD]
[TD="align: right"]382[/TD]
[TD="align: right"]417[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]465[/TD]
[TD="align: right"]304[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]482[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]454[/TD]
[TD="align: right"]398[/TD]
[TD="align: right"]160[/TD]
[/TR]
[TR]
[TD]Anguilla[/TD]
[TD="align: right"]2448[/TD]
[TD="align: right"]335[/TD]
[TD="align: right"]349[/TD]
[TD="align: right"]407[/TD]
[TD="align: right"]330[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]276[/TD]
[TD="align: right"]358[/TD]
[TD="align: right"]161[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]398[/TD]
[TD="align: right"]102[/TD]
[/TR]
[TR]
[TD]Antarctica[/TD]
[TD="align: right"]4958[/TD]
[TD="align: right"]193[/TD]
[TD="align: right"]212[/TD]
[TD="align: right"]389[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]187[/TD]
[TD="align: right"]236[/TD]
[TD="align: right"]159[/TD]
[TD="align: right"]323[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]336[/TD]
[/TR]
[TR]
[TD]Antigua:Barbuda[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]139[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]276[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]292[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]267[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]177[/TD]
[TD="align: right"]462[/TD]
[/TR]
[TR]
[TD]Argentina[/TD]
[TD="align: right"]1695[/TD]
[TD="align: right"]381[/TD]
[TD="align: right"]353[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]216[/TD]
[TD="align: right"]357[/TD]
[TD="align: right"]138[/TD]
[TD="align: right"]191[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]431[/TD]
[TD="align: right"]367[/TD]
[/TR]
[TR]
[TD]Armenia[/TD]
[TD="align: right"]3338[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]418[/TD]
[TD="align: right"]463[/TD]
[TD="align: right"]297[/TD]
[TD="align: right"]486[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]269[/TD]
[TD="align: right"]470[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]178[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Aruba[/TD]
[TD="align: right"]3933[/TD]
[TD="align: right"]363[/TD]
[TD="align: right"]478[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]373[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]418[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]490[/TD]
[TD="align: right"]316[/TD]
[TD="align: right"]381[/TD]
[TD="align: right"]121[/TD]
[/TR]
[TR]
[TD]Austral Oceania[/TD]
[TD="align: right"]3377[/TD]
[TD="align: right"]158[/TD]
[TD="align: right"]191[/TD]
[TD="align: right"]219[/TD]
[TD="align: right"]206[/TD]
[TD="align: right"]183[/TD]
[TD="align: right"]448[/TD]
[TD="align: right"]327[/TD]
[TD="align: right"]224[/TD]
[TD="align: right"]126[/TD]
[TD="align: right"]158[/TD]
[TD="align: right"]156[/TD]
[/TR]
[TR]
[TD]Australia[/TD]
[TD="align: right"]4751[/TD]
[TD="align: right"]436[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]224[/TD]
[TD="align: right"]316[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]462[/TD]
[TD="align: right"]124[/TD]
[TD="align: right"]455[/TD]
[TD="align: right"]446[/TD]
[TD="align: right"]149[/TD]
[/TR]
[TR]
[TD]Austria[/TD]
[TD="align: right"]2116[/TD]
[TD="align: right"]268[/TD]
[TD="align: right"]208[/TD]
[TD="align: right"]464[/TD]
[TD="align: right"]245[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]317[/TD]
[TD="align: right"]255[/TD]
[TD="align: right"]142[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]176[/TD]
[TD="align: right"]317[/TD]
[/TR]
[TR]
[TD]Azerbaijan[/TD]
[TD="align: right"]4982[/TD]
[TD="align: right"]344[/TD]
[TD="align: right"]410[/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]217[/TD]
[TD="align: right"]308[/TD]
[TD="align: right"]392[/TD]
[TD="align: right"]159[/TD]
[TD="align: right"]347[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]485[/TD]
[TD="align: right"]299[/TD]
[/TR]
[TR]
[TD]Bahamas[/TD]
[TD="align: right"]2681[/TD]
[TD="align: right"]308[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]285[/TD]
[TD="align: right"]232[/TD]
[TD="align: right"]382[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]451[/TD]
[TD="align: right"]64[/TD]
[/TR]
[TR]
[TD]Bahrain[/TD]
[TD="align: right"]4450[/TD]
[TD="align: right"]294[/TD]
[TD="align: right"]285[/TD]
[TD="align: right"]257[/TD]
[TD="align: right"]403[/TD]
[TD="align: right"]438[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]402[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]308[/TD]
[TD="align: right"]208[/TD]
[/TR]
[TR]
[TD]Bangladesh[/TD]
[TD="align: right"]516[/TD]
[TD="align: right"]316[/TD]
[TD="align: right"]362[/TD]
[TD="align: right"]323[/TD]
[TD="align: right"]328[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]252[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]371[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]148[/TD]
[/TR]
[TR]
[TD]Barbados[/TD]
[TD="align: right"]1047[/TD]
[TD="align: right"]146[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]487[/TD]
[TD="align: right"]275[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]472[/TD]
[TD="align: right"]363[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]253[/TD]
[/TR]
[TR]
[TD]Belarus[/TD]
[TD="align: right"]4249[/TD]
[TD="align: right"]462[/TD]
[TD="align: right"]436[/TD]
[TD="align: right"]244[/TD]
[TD="align: right"]428[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]449[/TD]
[TD="align: right"]318[/TD]
[TD="align: right"]460[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]191[/TD]
[/TR]
[TR]
[TD]Belgium[/TD]
[TD="align: right"]4547[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]323[/TD]
[TD="align: right"]287[/TD]
[TD="align: right"]304[/TD]
[TD="align: right"]487[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]374[/TD]
[/TR]
[TR]
[TD]Belgium-Luxem.[/TD]
[TD="align: right"]883[/TD]
[TD="align: right"]374[/TD]
[TD="align: right"]375[/TD]
[TD="align: right"]424[/TD]
[TD="align: right"]137[/TD]
[TD="align: right"]334[/TD]
[TD="align: right"]146[/TD]
[TD="align: right"]184[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]443[/TD]
[TD="align: right"]430[/TD]
[TD="align: right"]324[/TD]
[/TR]
</tbody>[/TABLE]



Sheet2

[TABLE="width: 819"]
<colgroup><col><col><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Countries[/TD]
[TD]Jan-Feb 2013[/TD]
[TD]Jan-Mar 2013[/TD]
[TD]Apr-May 2013[/TD]
[TD]Mar-Jul 2013[/TD]
[TD]Jan-Aug 2013[/TD]
[TD]Sep-Oct 2013[/TD]
[TD]Aug-Nov 2013[/TD]
[TD]Sep-Dec 2013[/TD]
[/TR]
[TR]
[TD]Abu Dhabi[/TD]
[TD="align: right"]3046[/TD]
[TD="align: right"]3380[/TD]
[TD="align: right"]238[/TD]
[TD="align: right"]949[/TD]
[TD="align: right"]4209[/TD]
[TD="align: right"]280[/TD]
[TD="align: right"]539[/TD]
[TD="align: right"]345[/TD]
[/TR]
[TR]
[TD]Afghanistan[/TD]
[TD="align: right"]2631[/TD]
[TD="align: right"]2709[/TD]
[TD="align: right"]555[/TD]
[TD="align: right"]1282[/TD]
[TD="align: right"]4303[/TD]
[TD="align: right"]480[/TD]
[TD="align: right"]1313[/TD]
[TD="align: right"]1160[/TD]
[/TR]
[TR]
[TD]Albania[/TD]
[TD="align: right"]925[/TD]
[TD="align: right"]1101[/TD]
[TD="align: right"]390[/TD]
[TD="align: right"]906[/TD]
[TD="align: right"]1886[/TD]
[TD="align: right"]654[/TD]
[TD="align: right"]1076[/TD]
[TD="align: right"]1292[/TD]
[/TR]
[TR]
[TD]Algeria[/TD]
[TD="align: right"]2267[/TD]
[TD="align: right"]2320[/TD]
[TD="align: right"]228[/TD]
[TD="align: right"]971[/TD]
[TD="align: right"]3362[/TD]
[TD="align: right"]553[/TD]
[TD="align: right"]987[/TD]
[TD="align: right"]921[/TD]
[/TR]
[TR]
[TD]American Samoa[/TD]
[TD="align: right"]1387[/TD]
[TD="align: right"]1801[/TD]
[TD="align: right"]829[/TD]
[TD="align: right"]1638[/TD]
[TD="align: right"]3325[/TD]
[TD="align: right"]316[/TD]
[TD="align: right"]692[/TD]
[TD="align: right"]836[/TD]
[/TR]
[TR]
[TD]Andorra[/TD]
[TD="align: right"]619[/TD]
[TD="align: right"]698[/TD]
[TD="align: right"]449[/TD]
[TD="align: right"]1221[/TD]
[TD="align: right"]2332[/TD]
[TD="align: right"]915[/TD]
[TD="align: right"]1747[/TD]
[TD="align: right"]1486[/TD]
[/TR]
[TR]
[TD]Angola[/TD]
[TD="align: right"]1517[/TD]
[TD="align: right"]1934[/TD]
[TD="align: right"]556[/TD]
[TD="align: right"]1375[/TD]
[TD="align: right"]3374[/TD]
[TD="align: right"]910[/TD]
[TD="align: right"]1790[/TD]
[TD="align: right"]1468[/TD]
[/TR]
[TR]
[TD]Anguilla[/TD]
[TD="align: right"]2783[/TD]
[TD="align: right"]3132[/TD]
[TD="align: right"]737[/TD]
[TD="align: right"]1392[/TD]
[TD="align: right"]4533[/TD]
[TD="align: right"]189[/TD]
[TD="align: right"]945[/TD]
[TD="align: right"]689[/TD]
[/TR]
[TR]
[TD]Antarctica[/TD]
[TD="align: right"]5151[/TD]
[TD="align: right"]5363[/TD]
[TD="align: right"]486[/TD]
[TD="align: right"]1121[/TD]
[TD="align: right"]6431[/TD]
[TD="align: right"]409[/TD]
[TD="align: right"]683[/TD]
[TD="align: right"]860[/TD]
[/TR]
[TR]
[TD]Antigua:Barbuda[/TD]
[TD="align: right"]205[/TD]
[TD="align: right"]344[/TD]
[TD="align: right"]339[/TD]
[TD="align: right"]802[/TD]
[TD="align: right"]1463[/TD]
[TD="align: right"]368[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]1007[/TD]
[/TR]
[TR]
[TD]Argentina[/TD]
[TD="align: right"]2076[/TD]
[TD="align: right"]2429[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]1066[/TD]
[TD="align: right"]3280[/TD]
[TD="align: right"]209[/TD]
[TD="align: right"]778[/TD]
[TD="align: right"]1007[/TD]
[/TR]
[TR]
[TD]Armenia[/TD]
[TD="align: right"]3410[/TD]
[TD="align: right"]3828[/TD]
[TD="align: right"]760[/TD]
[TD="align: right"]1723[/TD]
[TD="align: right"]5402[/TD]
[TD="align: right"]555[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]748[/TD]
[/TR]
[TR]
[TD]Aruba[/TD]
[TD="align: right"]4296[/TD]
[TD="align: right"]4774[/TD]
[TD="align: right"]475[/TD]
[TD="align: right"]1450[/TD]
[TD="align: right"]5816[/TD]
[TD="align: right"]806[/TD]
[TD="align: right"]1257[/TD]
[TD="align: right"]1308[/TD]
[/TR]
[TR]
[TD]Austral Oceania[/TD]
[TD="align: right"]3535[/TD]
[TD="align: right"]3726[/TD]
[TD="align: right"]425[/TD]
[TD="align: right"]1247[/TD]
[TD="align: right"]5109[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]835[/TD]
[TD="align: right"]664[/TD]
[/TR]
[TR]
[TD]Australia[/TD]
[TD="align: right"]5187[/TD]
[TD="align: right"]5342[/TD]
[TD="align: right"]337[/TD]
[TD="align: right"]1064[/TD]
[TD="align: right"]6713[/TD]
[TD="align: right"]579[/TD]
[TD="align: right"]1487[/TD]
[TD="align: right"]1174[/TD]
[/TR]
[TR]
[TD]Austria[/TD]
[TD="align: right"]2384[/TD]
[TD="align: right"]2592[/TD]
[TD="align: right"]709[/TD]
[TD="align: right"]1468[/TD]
[TD="align: right"]4107[/TD]
[TD="align: right"]267[/TD]
[TD="align: right"]698[/TD]
[TD="align: right"]760[/TD]
[/TR]
[TR]
[TD]Azerbaijan[/TD]
[TD="align: right"]5326[/TD]
[TD="align: right"]5736[/TD]
[TD="align: right"]362[/TD]
[TD="align: right"]1472[/TD]
[TD="align: right"]6957[/TD]
[TD="align: right"]388[/TD]
[TD="align: right"]1032[/TD]
[TD="align: right"]1172[/TD]
[/TR]
[TR]
[TD]Bahamas[/TD]
[TD="align: right"]2989[/TD]
[TD="align: right"]3086[/TD]
[TD="align: right"]517[/TD]
[TD="align: right"]1095[/TD]
[TD="align: right"]4212[/TD]
[TD="align: right"]151[/TD]
[TD="align: right"]730[/TD]
[TD="align: right"]666[/TD]
[/TR]
[TR]
[TD]Bahrain[/TD]
[TD="align: right"]4744[/TD]
[TD="align: right"]5029[/TD]
[TD="align: right"]660[/TD]
[TD="align: right"]1523[/TD]
[TD="align: right"]6371[/TD]
[TD="align: right"]423[/TD]
[TD="align: right"]835[/TD]
[TD="align: right"]939[/TD]
[/TR]
[TR]
[TD]Bangladesh[/TD]
[TD="align: right"]832[/TD]
[TD="align: right"]1194[/TD]
[TD="align: right"]651[/TD]
[TD="align: right"]1485[/TD]
[TD="align: right"]2344[/TD]
[TD="align: right"]419[/TD]
[TD="align: right"]466[/TD]
[TD="align: right"]587[/TD]
[/TR]
[TR]
[TD]Barbados[/TD]
[TD="align: right"]1193[/TD]
[TD="align: right"]1223[/TD]
[TD="align: right"]762[/TD]
[TD="align: right"]836[/TD]
[TD="align: right"]2501[/TD]
[TD="align: right"]407[/TD]
[TD="align: right"]883[/TD]
[TD="align: right"]664[/TD]
[/TR]
[TR]
[TD]Belarus[/TD]
[TD="align: right"]4711[/TD]
[TD="align: right"]5147[/TD]
[TD="align: right"]672[/TD]
[TD="align: right"]1595[/TD]
[TD="align: right"]6624[/TD]
[TD="align: right"]573[/TD]
[TD="align: right"]930[/TD]
[TD="align: right"]803[/TD]
[/TR]
[TR]
[TD]Belgium[/TD]
[TD="align: right"]4737[/TD]
[TD="align: right"]4800[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]716[/TD]
[TD="align: right"]5757[/TD]
[TD="align: right"]509[/TD]
[TD="align: right"]887[/TD]
[TD="align: right"]957[/TD]
[/TR]
[TR]
[TD]Belgium-Luxem.[/TD]
[TD="align: right"]1257[/TD]
[TD="align: right"]1632[/TD]
[TD="align: right"]561[/TD]
[TD="align: right"]1416[/TD]
[TD="align: right"]2857[/TD]
[TD="align: right"]793[/TD]
[TD="align: right"]1407[/TD]
[TD="align: right"]1547[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Reply to Post #13

I'd suggest splitting the header row in two rows on Sheet2 like this:

B1: 1-Jan-2013
B2: 1-Feb-2013

C1: 1-Jan-2013
C2: 1-Mar-2013

and so on.

Now we can have...

Sheet2, B3...

Control+shift+enter, copy across, and down.

=SUM(IF(Sheet1!$A$2:$A$100=$A3,IF(Sheet1!$B$1:$M$1>=B$1,
    IF(Sheet1!$B$1:$M$1<=B$2,Sheet1!$B$2:$M$100))))

The foregoing would be a much better approach than doing text operations on the header entries on Sheet2.

 

[jamilm]

Many thanks Aladin. The problem is that the headers in sheet2 aren't for two months what if I have eight months like in sheer2 header F1
It is from jan to aug . In this case do I need to split it in 8 rows?

Thanks again

 

[Aladin Akyurek]

Many thanks Aladin. The problem is that the headers in sheet2 aren't for two months what if I have eight months like in sheer2 header F1
It is from jan to aug . In this case do I need to split it in 8 rows?

Thanks again

You got me wrong...

Each From is in the first row and corresponding To immediately underneath in the second row... Thus:

F1: 1-Jan-2013
F2: 1-Aug-2013

The suggested formula reads these two as From-To.

 

[Aladin Akyurek]

Reply to Post #13

I'd suggest splitting the header row in two rows on Sheet2 like this:

B1: 1-Jan-2013
B2: 1-Feb-2013

C1: 1-Jan-2013
C2: 1-Mar-2013

and so on.

Now we can have...

Sheet2, B3...

Control+shift+enter, copy across, and down.

=SUM(IF(Sheet1!$A$2:$A$100=$A3,IF(Sheet1!$B$1:$M$1>=B$1,
    IF(Sheet1!$B$1:$M$1<=B$2,Sheet1!$B$2:$M$100))))

The foregoing would be a much better approach than doing text operations on the header entries on Sheet2.

Many thanks Aladin. The problem is that the headers in sheet2 aren't for two months what if I have eight months like in sheer2 header F1
It is from jan to aug . In this case do I need to split it in 8 rows?

Thanks again

You got me wrong...

Each From is in the first row and corresponding To immediately underneath in the second row... Thus:

F1: 1-Jan-2013
F2: 1-Aug-2013

The suggested formula reads these two as From-To.

See:

https://dl.dropboxusercontent.com/u/65698317/Sum%2BIf%20from-to%20jamilm.xlsx

 

[jamilm]

Thank you very much Aladin. this works. however, i have a problem where my Data in source sheet cannot always be in the same order.

for example in the sheet2 i switched Country names between Abu Dhabi and Afghanistan and then the formula value changed.

plz see https://www.dropbox.com/s/5755pbwx8lkf0zo/Sum+If from-to jamilm.xlsx

See:

https://dl.dropboxusercontent.com/u/65698317/Sum+If from-to jamilm.xlsx

 

[markmzz]

Thank you very much Aladin. this works. however, i have a problem where my Data in source sheet cannot always be in the same order.

for example in the sheet2 i switched Country names between Abu Dhabi and Afghanistan and then the formula value changed.

plz see https://www.dropbox.com/s/5755pbwx8lkf0zo/Sum+If from-to jamilm.xlsx

Try this small modification in Aladin's formula:

Control+Shift+Enter

=SUM(IF(Sheet1!$A$[COLOR="#FF0000"]1[/COLOR]:$A$100=$A3,IF(Sheet1!$[COLOR="#FF0000"]A[/COLOR]$1:$M$1 >= B$1,
IF(Sheet1!$[COLOR="#FF0000"]A[/COLOR]$1:$M$1<=B$2,Sheet1!$[COLOR="#FF0000"]A[/COLOR]$[COLOR="#FF0000"]1[/COLOR]:$M$100))))

Markmzz

 

[jamilm]

I am sorry, i made a mistake in entering the formula before. becuase when i rechecked even previous Formula provided by Aladin's was working perfectly.

Thank you very much Aladin and Markmzz.