Excel SUMIF(S)

When you have a list of data that needs to be added based on certain criteria, Excel has two functions to help: Sumif, which allows you to sum based on one criterion, and Sumifs for multiple criteria (up to 127!).

In this guide, we’ll use a table showing the top five countries to win gold medals in the last four Olympic games:

SUMIF

If we wanted to add up the number of gold medals that the United States has won in the past four Olympics, you could use the Sumif function.

Using the summary table, we can type “=sumif”.

There are three items for this formula:

• Range: the cells where we want to look for the criterion

• Criteria: the actual criterion we want to look for

• Sum Range: the cells that we want to add up. Note that this is an optional item, since the Range and Sum Range could be the same (it isn’t for this example, however).

For our example, our Range is Column D. We will reference the cell that says, “United States” for our Criteria. Finally, the Sum Range is Column E.

When we press Enter, the answer is given to us (110).

SUMIFS

If you need to sum on more than one criterion, Excel has a second formula called Sumifs. This allows you to specify multiple criteria and criteria ranges.

By typing “=sumifs” you can see that the format of this function is slightly different than Sumif function. The first thing listed is the Sum Range, which came last in the Sumif function. The reason for the change is that we can now add lots of criteria, so Excel needs to have the Sum Range reference first.

If we wanted to see the breakdown of the medals won in the Summer versus Winter Olympics, we could do a Sumifs function referencing the related cells and columns:

If we drag down the formula to row 6, we see that the United States has won 92 gold medals in the last two summer games, compared to 18 in the last two winter games.

Those are the basics for these two formulas. Watch the video above and download the exercise file to see these examples as well as see an expanded data set that shows all Olympic medal winners in modern Olympic history.