Calculate annual sales per country

[Subroto]

The dataset is in I8:M25. What I am required to calculate is "annual sales per country" based on the data set.

The solution should be as per the field I29:J29. It should be solved with (formula only).
I would be happy if someone comes forward to help in solving it.

The fields are:

Country

YEAR

Sales

Calculate annual sales.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3		Ques. 2	Filter data from Database A to Database B for relevant IDs. In case information is not available,							4 Marks
4			please update the cell as "No Data Available"
5			Calculate annual sales per country
6		Database A							Database B
7
8		ID	Country	YEAR	Month	Sales			ID	Country	YEAR	Month	Sales
9		48906370343	AUSTRIA	2011	1	6,315			1614005737	SWEDEN	2010	4	2750
10		48906349573	AUSTRIA	2010	2	7,447			1614105110	SWEDEN	2010	1	7595
11		1660100002	GERMANY	2010	1	74,305			1327073134	UK	2011	3	6269
12		000003S6530	Australia	2010	1	57,997			1307100158	UK	2010	1	16843.4
13		000003S6540	Australia	2011	1	3,200			1550079668	GERMANY	2010	2	21145.98
14		000003S6560	Australia	2011	2	43,343			1550082381	GERMANY	2010	3	137
15		000003S6570	Australia	2011	3	80,898			1780534853	No Data Available	No Data Available	No Data Available	No Data Available
16		000003S6580	Australia	2011	4	15,069			1660100002	GERMANY	2010	1	74305
17		000003S6595	Australia	2010	2	1,191			1614122656	SWEDEN	2010	1	44765
18		1550079668	GERMANY	2010	2	21,146			1614005921	SWEDEN	2011	5	9940
19		1550082381	GERMANY	2010	3	137			1614005922	SWEDEN	2011	5	43215
20		1550074871	UK	2010	4	5,921			1332771087	UK	2011	1	35130.64
21		1550075136	GERMANY	2011	1	100			1327071086	UK	2011	1	66
22		1550075141	GERMANY	2011	2	0			1721596900	No Data Available	No Data Available	No Data Available	No Data Available
23		1550071047	GERMANY	2011	3	472			1550074871	UK	2010	4	5921.25
24		1550071041	UK	2011	4	1,895			1783173067	NETHERLANDS	2011	1	1922.97
25		1550071173	GERMANY	2011	5	134			1614003679	SWEDEN	2011	1	85515
26		1550071245	GERMANY	2011	6	0
27		1550072975	UK	2011	7	200
28		1550070862	UK	2011	8	0			Calculate annual sales per country
29		1550071249	UK	2011	9	2,114			Country	YEAR	Sales
30		1550071033	UK	2011	10	3,646
31		1550071035	UK	2011	11	100
32		1550070895	UK	2011	12	0
33		1550070896	UK	2011	1	260
34		1550070897	UK	2011	2	105
35		1550070854	UK	2011	3	0
36		1550072992	GERMANY	2011	4	50
37		1550071116	GERMANY	2011	5	1,510
38		1550075131	GERMANY	2011	6	105
39		1550071107	UK	2010	5	214
40		1550075144	UK	2010	6	42
41		1550071049	UK	2010	7	0
42		1550075137	UK	2010	8	0
43		1550071213	UK	2010	9	602
44		1550071004	UK	2010	10	42
45		1550071051	UK	2010	11	0
46		1550071063	GERMANY	2010	12	0
47		1550071070	GERMANY	2010	1	8
48		48867012335	GERMANY	2010	2	86,031
49		1700683877	GERMANY	2010	3	936
50		1700671080	GERMANY	2010	4	7,397
51		1780534854	NETHERLANDS	2010	1	11,574
52		1783173067	NETHERLANDS	2011	1	1,923
53		1780507234	NETHERLANDS	2011	1	971
54		1780538253	NETHERLANDS	2011	1	601
55		1782938199	NETHERLANDS	2011	12	7,173
56		1782138202	NETHERLANDS	2011	12	8,849
57		1782938210	NETHERLANDS	2011	5	380
58		1782138229	NETHERLANDS	2011	2	317
59		1780532883	NETHERLANDS	2010	1	1,999
60		1782132891	NETHERLANDS	2010	1	991
61		1654006486	SWEDEN	2010	1	10,700
62		48943403913	SWEDEN	2010	1	66,480
63		48943602747	SWEDEN	2010	1	95,772
64		1721596901	SPAIN	2010	1	5,998
65		1720274284	SPAIN	2011	1	42,056
66		1720297646	SPAIN	2010	1	3,685
67		1720297643	SPAIN	2010	2	484
68		1614005737	SWEDEN	2010	4	2,750
69		1614105110	SWEDEN	2010	1	7,595
70		1614122656	SWEDEN	2010	1	44,765
71		1614005921	SWEDEN	2011	5	9,940
72		1614005922	SWEDEN	2011	5	43,215
73		1614003679	SWEDEN	2011	1	85,515
74		1614004035	SWEDEN	2011	1	45,482
75		1614004033	SWEDEN	2011	1	3,465
76		1614004408	SWEDEN	2011	3	1,700
77		1614004059	SWEDEN	2011	1	16,875
78		1614004034	SWEDEN	2011	5	3,483
79		1614004056	SWEDEN	2010	1	7,396
80		1614004752	SWEDEN	2010	1	78,800
81		1761290840	SWITZERLAND	2010	10	337
82		1761291502	SWITZERLAND	2011	1	2,012
83		1327073134	UK	2011	3	6,269
84		1307100158	UK	2010	1	16,843
85		1332771087	UK	2011	1	35,131
86		1327071086	UK	2011	1	66
87
Ques

Cell Formulas
Range	Formula
J9:M25	J9	=IFERROR(VLOOKUP($I9,$B$9:$F$86,COLUMN(B2),0),"No Data Available")

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Ques!$B$8:$F$86	J15

 

[Kaper]

Looks like a school assignment.
Anyway, my proposition to have it universal would be:
I30:

=UNIQUE(C9:C86)

J29:

=TRANSPOSE(UNIQUE(D9:D86))

J30 (no need to copy it's a spill formula):

=SUMIFS(F9:F86,C9:C86,I30#,D9:D86,J29#)

 

[AhoyNC]

You may want to look at the GROUPBY function.

 

[Subroto]

You may want to look at the GROUPBY function.

Thanks this is what exactly I was searching for.

 

[Subroto]

Looks like a school assignment.
Anyway, my proposition to have it universal would be:
I30:

=UNIQUE(C9:C86)

J29:

=TRANSPOSE(UNIQUE(D9:D86))

J30 (no need to copy it's a spill formula):

=SUMIFS(F9:F86,C9:C86,I30#,D9:D86,J29#)

Thank you! I will remember this trick. But actually, I want the YEARS in one column. Anyway, I got a function to deal with it which is GROUPBY function.

 

[AhoyNC]

You're welcome. Glad we could help. Thanks for the feedback