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:
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 |