For basic tallying and totaling, the SUM function is a pretty good workhorse (you can review SUM function basics here), but often more control is needed. When you have a mixed list and need to add up only a certain type, SUMIF and SUMIFS be enormously powerful tools. At its most basic, SUMIF lets you pick which cells in a row or column to include in the sum, based on a defining characteristic. Let’s explore how it works…

SUMIF Function Basics

SUMIF is the simpler of the two functions. The syntax of SUMIF is as follows:

=SUMIF(range, criteria, [sum_range])

The range input is the set of cells that SUMIF will test to see if they qualify for the sum.

The criteria input is the test that will be applied. It can be a numerical condition, like a match or a “greater than” check. It can also be a text match on part or all of the range.

The sum_range input is optional. If there is no sum_range, SUMIF will add the cells in the range that meet the criteria. If a sum_range is provided, SUMIF will test the range and sum the corresponding cell in the sum_range. This is important, and we’ll explore it in a moment. For now, let’s look at a simple example.

SUMIF Real Estate Property Example

Pretend that you are running a real estate brokerage and you have several houses up for sale in various parts of the city. You know their addresses, their neighborhoods, and their individual prices. But what if you wanted to know the total property value for each neighborhood? What about houses of a certain price?

[iframe width=”620″ height=”500″ frameborder=”0″ scrolling=”no” src=”https://skydrive.live.com/embed?cid=56D8503869383D0E&resid=56D8503869383D0E%21114&authkey=AA9SYQH4CjQGr8Y&em=2&AllowTyping=True&wdDownloadButton=True”]

In the above example, we are exploring several different ways that the SUMIF function can be used. Let’s start at the bottom and work upwards.

SUMIF Using Relations

Double-click on cell D19 and look at the formula:

=SUMIF(C:C,"<750000")

Here we are trying to find the total property value for all houses that are selling for less than $750,000. We ask SUMIF to check all of column C for values that are smaller than 750,000 (don’t forget the quotations “” around the less-than term). Since there is no sum_range here, SUMIF adds all the numbers in column C that qualify.

We could just have easily asked for the total of houses greater than $750,000 (“>750000”), or less-than or equal to $600,000 (“<=600000”). Any relation will work. If we just want those houses that are priced at a fixed value, we don’t need to specify the equal (=) sign or the quotes. Try changing around the formula in the example worksheet above to explore different results.

SUMIF Using String Matches

Now double-click on cell D18 and look at the formula:

=SUMIF(A:A,"* 4th*",C:C)

Now we’re getting a bit more complicated. Here we want to find the total property value for all houses on 4th Street. They are not all in the same neighborhood, so we have to look at the street address. We ask SUMIF to check all of column A for values that have 4th in them.

The asterisks (*) are wildcards. They mean that we don’t care what comes before and what comes after 4th in the cell. This is tricky, though, because there is a house on 14th Street that could be a match. Therefore, we have to include a space before the 4th to specify that we really only want 4th and not 14th.

Since we are looking for the sum of property values and not street addresses, we need to include a sum_range. This time, we are looking to add up the items in column C. SUMIF knows to only add the cells that are in the same row as those that match the criteria from the formula.

SUMIF Using Cell Matches

Now double-click on one of the cells between D12 and D15 and look at the formula (I’ll show D12 here):

=SUMIF(B:B,E12,C:C)

Here we want to know the total property value in each of the neighborhoods. We need to look at the neighborhood column, so we ask SUMIF to look in all of column B. We could specify the neighborhood we want to look for by typing it in (for example, “Capitol Hill”), but these numbers are going to need a label to understand them anyway. Since the next cell has the Capitol Hill label, all we have to do is ask SUMIF to look for a match to the contents of cell E12.

This gives us a change play with the dynamic ability of SUMIF (and Excel in general). In the example worksheet above, change cell E12 to say Fremont instead of Capitol Hill and watch it change the calculation in cell D12. What do you see? Excel automatically changes the calculation to sum the total for the new neighborhood.

Using cell references to evaluate SUMIF is a great way to eliminate hidden typos and other errors. You can check your results right in the worksheet without having to dive into formulas. Also, notice that you can just enter the formula once in D12 and copy it down to D15. Excel knows to change the cell reference, and that saves you time!

SUMIF Using Internal References

One final example… Double-click on D2 and look at the formula:

=SUMIF(B:B,B2,C:C)

This is very similar to the last example using cell matches. The main difference to note is that the cell SUMIF is matching the range to (B2) is inside the range (column B). This is powerful because it means that aggregate information can be stored inside a data table. This is useful for calculating percentages, ratios, and other analytical data dynamically inside the table. When you need to add or delete rows, the figures will re-calculate, saving time and keeping your calculations accurate!

As your projects become more complex, sometimes you need to check for multiple criteria in your sums. For this, there is the big brother of SUMIF, called SUMIFS. We’ll explore SUMIFS and more from the SUM function family soon!