Forum Discussion
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
Hi elinsnoring ,
You may create Pivot Table on first columns as attached adding more cosmetic to it.
Hi SergeiBaklan,
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.
