SUMIF

[elinsnoring]

Dear Community, 

I would greatly appreciate some help with conducting a (in my mind) quite challenging task in excel. So, below you find an example of my spreadsheet. In the first column you find the company name (A or B), in the second column you find which countries these companies are active in, the third column states if the country is a EU- or non-EU country, column four gives us the number of emissions that each company emits in each country, and the fifth column states the year of the emission activities. 

As you can see, the countries of operation differ between company A and B in both 2018 and 2019, and also company A operates in one less country in 2018, whereas company B operates in one more. 

So, what I would like to do is aggregate the emissions data for each company, and each year and have this number aggregated for EU emissions and non-EU emissions respectively. Could you please instruct me on how to do this?

Thank you so much in advance!

Company	Country	EU =1, non-EU = 0	Emissions (metric tons)	Year	Emissions in EU per company and year	Emissions ouside EU per company and year
A	Sweden	1	10	2018
A	Denmark	1	12	2018
A	Australia	0	30	2018
A	USA	0	65	2018
B	Sweden	1	10	2018
B	Denmark	1	12	2018
B	Australia	0	30	2018
B	USA	0	65	2018
B	United Kingdom	1	22	2018
A	Sweden	1	13	2017
A	France	1	50	2017
A	India	0	2	2017
A	Poland	1	5	2017
A	USA	0	20	2017
B	USA	0	20	2017
B	Sweden	1	13	2017
B	Argentina	0	8	2017
B	France	1	50	2017

[#2]

Hi @elinsnoring ,

You may create Pivot Table on first columns as attached adding more cosmetic to it.

[#3]

Hi @Sergei Baklan,

Thank you vey much for this. 

However, as my full sample contains approximately 2 400 companies operating in around 150 countries each I am not sure if the approach you describe is applicable here. Do you consider the Pivot Table to be a good approach when the sample is of such as size or do you know of any other method to use? 

All the best,

Elin

[#4]

Hi Elin,

PivotTable is natural way for such kind of summaries, you filter and total it in different ways, create few PivotTable to show results from different views. Additionally you may add charts to illustrate such results.

2400x150 gives 360,000 records, it shall work.

If it's enough for you only translate 1 and zero into emissions in last two columns that's simple IF() as attached. Sure, you may summarise by formulas, same SUMIFS(), but for that you you need to design resulting table(s) into which to summarize, how they looks like. As I understood in your table combination of Company/Country/Year are not repeating.

[#5]

Hi @Sergei Baklan, 

Thank you for the prompt reply. 

As this data will be transported into STATA I think that I should go for the SUMIF option. I am a bit unsure if STATA can read the Pivot Table.

Do you by any chance know how I can summarise the EU- and non-EU emissions data by company? That is, a command saying sum emissions in EU by company and sum emissions outside of EU by company?

[#6]

@elinsnoring , it depends on how you design your resulting table, but in any case you need a list of companies.

For such sample

first formula will be

=SUMIFS($G$4:$G$21,$B$4:$B$21,$K4,$F$4:$F$21,L$2,$D$4:$D$21,1)

, rest in the row are similar, and drag them down. Please see second sheet in attached.

[#7]

Hi @Sergei Baklan, 

Thank you so much, you made my day!

[#8]

Elin, you are welcome