Sum subtotals in another row...

[Excellent Excel]

Hi,

Suppose I have the following data:

Region	Country	Revenue A (per region)	Revenue B (per country)	Country totals	General total
Oslo	Norway	1.000	50.000	0	1.000
Trondheim	Norway	2.000	50.000	0	2.000
Bergen	Norway	3.000	50.000	0	3.000
Stockholm	Sweden	4.000	80.000	0	4.000
Gotenburg	Sweden	5.000	80.000	0	5.000
Londen	England	4.000	80.000	0	4.000
Manchester	England	2.000	65.000	0	2.000
Cambridge	England	1.000	65.000	0	1.000
Final	Final			195.000	195.000

Columns "Region", "Country", "Revenue A (per region)" and "Revenue B (per country)" are an example of data, similar like in my real file. I need the column "General total", which is no problem (just the sum of column "Revenue A (per region)" and column "Country totals", but I can't figure out how I can get the figures of "Country totals". Country totals is just the total of each country from column "Revenue B (per country)".

Can someone please help me out with this?

Thanks a lot in advance!

 

[Excellent Excel]

Of course, if I can have a formula that gets me the 195.000 in each row, I'm happy too, then I can create another column in which I use a filter to have the 195.000 only in the row "Final". Thanks!

 

[Toadstool]

Hi Excellent Excel,

I see Revenue B (per country) for Londen England is 80.000 but should it be 65.000?
Will the same Country values be grouped together, as in the example?

 

[Excellent Excel]

Hi Toadstool,

My bad, you are right, London England should also be 65.000. (can't I correct that in my original post?) Yes, the same country values are grouped together as in column "Revenue B (per country)". So column "Country totals" should take the sum per country, so 50.000 for Norway + 80.000 for Sweden + 65.000 for England= 195.000.

Thank you!

 

[Toadstool]

Does this do what you ask?

Excellent Excel.xlsx
	A	B	C	D	E	F
1	Region	Country	Revenue A (per region)	Revenue B (per country)	Country totals	General total
2	Oslo	Norway	1.000	50.000	0.000	1.000
3	Trondheim	Norway	2.000	50.000	0.000	2.000
4	Bergen	Norway	3.000	50.000	50.000	3.000
5	Stockholm	Sweden	4.000	80.000	0.000	4.000
6	Gotenburg	Sweden	5.000	80.000	80.000	5.000
7	Londen	England	4.000	80.000	0.000	4.000
8	Manchester	England	2.000	65.000	0.000	2.000
9	Cambridge	England	1.000	65.000	65.000	1.000
10	Final	Final			195.000
Sheet1

Cell Formulas
Range	Formula
E2:E9	E2	=IF(B2<>B3,D2,0)
E10	E10	=SUM(E2:E9)

 

[Excellent Excel]

Hi Toadstool,

I think we have a miscommunication... Based on your reply I think I misunderstood you when you were asking if the country values will be grouped together or not. What I meant/need is the exact same column "Country Totals" as in my example, so all should be 0 except the last row "Final"). There are actually 2 options I can live with, either my "Country totals"-column looks like this:

Region	Country totals
Oslo	0
Trondheim	0
Bergen	0
Stockholm	0
Gotenburg	0
London	0
Manchester	0
Cambridge	0
Final	195.000

or it looks like this:

Region	195.000
Oslo	195.000
Trondheim	195.000
Bergen	195.000
Stockholm	195.000
Gotenburg	195.000
London	195.000
Manchester	195.000
Cambridge	195.000
Final	195.000

Either of these 2 options is valid for me.

Please note: this is Power Pivot, not in Excel!

Thanks!

 

[Toadstool]

My mistake.

 

[Excellent Excel]

No problem, thanks anyway! Hopefully someone can help anyway...