Most people know how to use Excel to add the numbers in a column. But what if you want to add only the numbers in a column that meet a certain criteria? There are a couple of ways that can be accomplished, (aside from manually selecting cells yourself) but the simplest is usually to use the SUMIF function.
For our example, lets say you have a spreadsheet of sales sorted by date and you don’t want to change that. But you need to get totals for each sales region, and all regions are mixed together in that same list. If the amount is in column D, the sales region is in column E, and our data is in rows 2 through 25, here is how you can get the totals you need.
First, go to the cell where you want the total. Then click the function wizard button. (It is usually just above the column headings , next to the formula bar and looks like a small F with an x after it.) Type SUMIF in the search box and press Enter (or click the Go button) and then click OK. That will open a window that will help you build the formula.
At Range, you want to enter the range of cells that hold the information that you want Excel to evaluate to determine if the sales amount should be included in your total. In this example, that would be rows 2 through 25 of column E (the sales region column). So you can either highlight those cells with your mouse, or type E2:E25 in the Range box.
Criteria is where you tell Excel what to look for in the range we just gave it. Let’s say that our sales territories are North, South, East, and West. So here, just type the word North in the Criteria box. You could also enter the location of a cell that contains the word North. If we typed North in the cell next to our total, we could have entered that cell’s location in Criteria instead of typing the word North.
Sum_range is the range of cells that contains the numbers that we wanted added (if they meet the criteria). So you can type D2:D25 here or highlight column D, rows 2 through 25 (the sales amounts) with your mouse.
Notice that, to the right of each entry, Excel shows you the result of what you entered. And beneath that, the result for the formula. When you click OK, the formula will be entered in the cell for you. You will now have a total of all sales for the North region.
I used a very simple criteria in this example, but you can use more complex comparisons. For example, entering >P in the criteria would total the sales if the region comes after P alphabetically (South and West in our example). So don’t be afraid to experiment and see what kind of spreadsheet problems you can solve using SUMIF.
This is an update of a post from July 2010.
