• 469K Members
• 4,581 Online
• 567K Conversations

New Contributor

# SUMIF

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

# Re: SUMIF

Hi @elinsnoring ,

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

# Re: SUMIF

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

# Re: SUMIF

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.

# Re: SUMIF

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?

# Re: SUMIF

@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.

# Re: SUMIF

Thank you so much, you made my day!

# Re: SUMIF

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