The Sumif function in Excel is one of the easiest to learn, yet also extremely powerful. Very often in Excel, there are multiple dimensions in your data, and you’re looking to sum the data IF certain criteria are present. Is the sales branch on the west coast or east? Is the data for March or April? Are we summing Q1 or Q3. ALL of those scenarios are possible to calculate using SUMIF

Before we learn how SUMIF works, let’s make sure we first understand how the SUM function works.

The SUM Function

The SUM function takes in a list of data (or an array) and simply adds them up. It’s one of the most commonly used functions in Excel, especially for beginners.

Below we’ve used sum to find the total number of sales across all employees: $35,633.26

However, what if we wanted to add up the amount of sales that certain sections of our employees had, based on multiple criteria? SUMIF!

The SUMIF Function

Sumif is a formula you can use to find the sum IF a certain criteria is true.

In this first example, if the sales person averaged more than $1000 in sales each day, we’re summing up the total amount of sales: $20,604.71

Notice that when limiting the summed values by number, the “>1000” is in quotes:

The SUMIF Function: Example 2

What if we wanted to get a bit more complicated with how we summed our numbers? In our case, let’s try summing only those daily sales numbers that appear in the west branch.

To accomplish this, we have to choose a new range, that of the list of Branch offices (column E). Within Column E, we’ll look for “West Branch” (consistent naming conventions are key here), and lastly we specify the range to sum.

In other words, we’re running a little program against the data:

Step 1 – Check Column E for the string “West Branch”
Step 2 – Sum together the corresponding value in column D
Step 3 – Continue the process for each value that matches that criteria

The total: $8242.11

Our final formula looks like this:

=SUMIF(E2:E106,"West Branch",D2:D106)