Quick Navigation

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!

How would you add the numbers in a column whose row number is greater than 4, for example?

That is not exactly what I want to do but that would help me to find the solution.

Thank you