SUMIF

elinsnoring
New Contributor

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!

 

CompanyCountryEU =1, non-EU = 0Emissions (metric tons)YearEmissions in EU per company and yearEmissions ouside EU per company and year
ASweden1102018  
ADenmark1122018  
AAustralia0302018  
AUSA0652018  
BSweden 1102018  
BDenmark1122018  
BAustralia0302018  
BUSA0652018  
BUnited Kingdom1222018  
ASweden 1132017  
AFrance1502017  
AIndia022017  
APoland152017  
AUSA0202017  
BUSA0202017  
BSweden1132017  
BArgentina082017  
BFrance1502017  
7 Replies

Hi @elinsnoring ,

 

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

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

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.

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?

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

For such sample

image.png

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.

Hi @Sergei Baklan

 

Thank you so much, you made my day!

 

Elin, you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies