Natural Disasters - what is the cost? (and can we use a SUMIF to find out?)

Hello all. Today my hometown was flooded. Badly. I live up on a hill, so avoided the floods, but was without water and electricity for most of the day. However, a lot of people will have lost their possesions and their business stock but thankfully not their lives. My heart has been with them.

I am also a geek, so after getting over the irritation of being inconvenienced by Mother Nature,  I started wondering about the cost of natural disasters. I found this helpful map, courtesy of The Economist and Munich Re, on the top 10 most costly natural disasters:





In table format, the economic stats look like this (please note that if there are 2 disasters combined, I have just split the costs equally):

Scary stuff.  But lets try analyse it a bit. So what if you wanted to see which disaster type costs the most, in total and on average? You would do this:



I have added in the formulas for the first row (Earthquakes) so that you can see how I came to the figures. Now, before you start praying for a natural disaster to sweep me and my formulas away, let me try explain them in English.

What a SUMIF does is look for stuff in a column within a table of data that matches a criteria you specify, and when it does find those items, it sums up all the corresponding figures.

So for the earthquake line, to add up all the costs of earthquakes, you are saying =SUMIF(look in the Disaster column, for all the Earthquakes, now add up the corresponding costs).

COUNTIF works pretty much the same, except it just counts the number of times the specific criteria appears. So to count the number of times Earthquakes appear on the list, you say =COUNTIF(look in the disaster column, count how many times Earthquake appears).

Easy as pie. The average calculation then just flows from the two answers, and indicates that after Earthquakes, Tsunamis are not be trifled with in terms of economic damage.

Now.....what if you wanted to check the total costs just in Japan, pre and post 2000?

A SUMIFS  formula follows the same concept as a single SUMIF, you can just add more criteria. Also, the order changes in the formula. So to sum all the costs in Japan pre 2000, you say: =SUMIFS(these are the costs to add up, when anything in the Year column, is less than 2000, and anything in the country column, says Japan).

It really will make sense when you get the hang of talking to yourself when you're working....!

So that's your brain bender for this week. In this week's newsletter I will be discussing some other useful Count functions. Sign up here if you would like to learn more....

Happy spreadsheeting, and stay safe and dry!