Calculate annual sales per country

S

Subroto

New Member
Joined
Aug 15, 2024
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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:

CountryYEARSales


Calculate annual sales.xlsx
ABCDEFGHIJKLMN
1
2
3Ques. 2Filter data from Database A to Database B for relevant IDs. In case information is not available,4 Marks
4please update the cell as "No Data Available"
5Calculate annual sales per country
6Database ADatabase B
7
8IDCountryYEARMonthSalesIDCountryYEARMonthSales
948906370343AUSTRIA201116,3151614005737SWEDEN201042750
1048906349573AUSTRIA201027,4471614105110SWEDEN201017595
111660100002GERMANY2010174,3051327073134UK201136269
12000003S6530Australia2010157,9971307100158UK2010116843.4
13000003S6540Australia201113,2001550079668GERMANY2010221145.98
14000003S6560Australia2011243,3431550082381GERMANY20103137
15000003S6570Australia2011380,8981780534853No Data AvailableNo Data AvailableNo Data AvailableNo Data Available
16000003S6580Australia2011415,0691660100002GERMANY2010174305
17000003S6595Australia201021,1911614122656SWEDEN2010144765
181550079668GERMANY2010221,1461614005921SWEDEN201159940
191550082381GERMANY201031371614005922SWEDEN2011543215
201550074871UK201045,9211332771087UK2011135130.64
211550075136GERMANY201111001327071086UK2011166
221550075141GERMANY2011201721596900No Data AvailableNo Data AvailableNo Data AvailableNo Data Available
231550071047GERMANY201134721550074871UK201045921.25
241550071041UK201141,8951783173067NETHERLANDS201111922.97
251550071173GERMANY201151341614003679SWEDEN2011185515
261550071245GERMANY201160
271550072975UK20117200
281550070862UK201180Calculate annual sales per country
291550071249UK201192,114CountryYEARSales
301550071033UK2011103,646
311550071035UK201111100
321550070895UK2011120
331550070896UK20111260
341550070897UK20112105
351550070854UK201130
361550072992GERMANY2011450
371550071116GERMANY201151,510
381550075131GERMANY20116105
391550071107UK20105214
401550075144UK2010642
411550071049UK201070
421550075137UK201080
431550071213UK20109602
441550071004UK20101042
451550071051UK2010110
461550071063GERMANY2010120
471550071070GERMANY201018
4848867012335GERMANY2010286,031
491700683877GERMANY20103936
501700671080GERMANY201047,397
511780534854NETHERLANDS2010111,574
521783173067NETHERLANDS201111,923
531780507234NETHERLANDS20111971
541780538253NETHERLANDS20111601
551782938199NETHERLANDS2011127,173
561782138202NETHERLANDS2011128,849
571782938210NETHERLANDS20115380
581782138229NETHERLANDS20112317
591780532883NETHERLANDS201011,999
601782132891NETHERLANDS20101991
611654006486SWEDEN2010110,700
6248943403913SWEDEN2010166,480
6348943602747SWEDEN2010195,772
641721596901SPAIN201015,998
651720274284SPAIN2011142,056
661720297646SPAIN201013,685
671720297643SPAIN20102484
681614005737SWEDEN201042,750
691614105110SWEDEN201017,595
701614122656SWEDEN2010144,765
711614005921SWEDEN201159,940
721614005922SWEDEN2011543,215
731614003679SWEDEN2011185,515
741614004035SWEDEN2011145,482
751614004033SWEDEN201113,465
761614004408SWEDEN201131,700
771614004059SWEDEN2011116,875
781614004034SWEDEN201153,483
791614004056SWEDEN201017,396
801614004752SWEDEN2010178,800
811761290840SWITZERLAND201010337
821761291502SWITZERLAND201112,012
831327073134UK201136,269
841307100158UK2010116,843
851332771087UK2011135,131
861327071086UK2011166
87
Ques
Cell Formulas
RangeFormula
J9:M25J9=IFERROR(VLOOKUP($I9,$B$9:$F$86,COLUMN(B2),0),"No Data Available")
Named Ranges
NameRefers ToCells
_FilterDatabase=Ques!$B$8:$F$86J15
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Looks like a school assignment.
Anyway, my proposition to have it universal would be:
I30:
Excel Formula: 
=UNIQUE(C9:C86)
J29:
Excel Formula: 
=TRANSPOSE(UNIQUE(D9:D86))
J30 (no need to copy it's a spill formula):
Excel Formula: 
=SUMIFS(F9:F86,C9:C86,I30#,D9:D86,J29#)
 
Upvote 0
Kaper said:
Looks like a school assignment.
Anyway, my proposition to have it universal would be:
I30:
Excel Formula: 
=UNIQUE(C9:C86)
J29:
Excel Formula: 
=TRANSPOSE(UNIQUE(D9:D86))
J30 (no need to copy it's a spill formula):
Excel Formula: 
=SUMIFS(F9:F86,C9:C86,I30#,D9:D86,J29#)
Click to expand...

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.
 
Upvote 0
You're welcome. Glad we could help. Thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
trending value projections without counting sundays
Replies
2
Views
290
KRice
K
G
  • Question Question
Checking Mutual Dates for Annual Holiday Planning
Replies
2
Views
153
arun_eagertolearn
A
K
  • Question Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
514
XLfriend777
XLfriend777
L
  • Question Question
Send cell values to 2 other sheets according to condition
Replies
2
Views
199
leobrice
L
A
  • Question Question
create report for 40000 rows to calculation stock across sheet based on sheet name in cell value
Replies
1
Views
349
Amer Omar
A

Forum statistics

Threads
1,223,879
Messages
6,175,145
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top