Share this article:

The Sum formula is one of the most common formulas that you’ll use in Excel. This formula allows you to add up the values within a range or sum a number of values separated by a comma. Either way is fine.

Sum()

Format: Sum(A1:D4) or Sum(A1,B1)

Here we’ve used sum to find the total number of sales across all employees:

SUM function

Sumif()

Format – Sumif(range, criteria, [sum range] )

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’d summed up everything. Sum if can be a way to only sum those values that are higher or lower than a certain range.

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

download (1)

SUMIF(): Part 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

Our final formula looks like this:

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

SUMIF