Introducing the SUMIF Command in Microsoft Excel 2007

Well, one of the natural ways to summarize data in Excel is to use functions. I am not just talking about the Sum functions. I am talking about more powerful functions like the SUMIF function that allows us to conditionally calculate.

Let us open the next project file, open the SUMIF file. Now, this is a simple data set with region, product, date, quantity, unit price, unit cost, revenue and cost and we want to be able to create some conditional sums of this data. I will hit the End+Down key to go down to the end of my data set. You will see that we have 45 rows of data plus a heading or row 46. I would like to build a table underneath this data set with total revenue for each region; Central, East and West. Let me arrow down a little bit and you will see that we have the beginning of this table, already built. Select cell 49 where we will build a SUMIF function in order to summarize data for just the East Region.

The function that we want to use is the SUMIF function. So every formula in Excel has to start with equal sign. Type the “=” sign and type SUMIF. And you will see that new in Excel 2007, the auto complete gives us a list of all the functions that start with SUMIF. In this case, we want to use the original SUMIF function. Later on in this lesson we will learn about the new SUMIFS function. Type the opening parenthesis and Excel tells us that first, we want to look through a particular range. And so that is the range in our data set that contains the regions. In this case, that would be A2 through A46. I am going to select that by first, clicking on A46 and then using the Shift and Up to select the range A2 to A46.

Now I want to make sure that as I copy this formula later, I always point to A2 to A46. I am going to hit the F4 key to make that an absolute reference. You will see that Excel adds the dollar signs in. Type the comma and next, Excel says that we are looking for the criteria. The criteria is what value we are trying to match in our region field. That field is immediately to the left of where I am entering the formula so use the Left arrow key to highlight cell B49. As I copy this formula down, I want to allow this reference to point to B50 and B51, so no dollar signs here, type a comma and Excel says that we now need the sum range.

The sum range has to be the exact same shape as the first argument and it should be the column that you are trying to sum. In our case, that is the revenue column that were in column G, so I want to select from G2 to G46. I will click on G2. Use the Shift and Up key; making sure to point to G2 to G46 and finally, hitting the F4 key to make that be an absolute reference. Type the closing parenthesis, Control+Enter and we will see that our formula totals just the East Region; 273,202.

Now, if we have done our job right, we should be able to copy this formula down to the next two rows and it will calculate the total for the Central Region and West Region. Fast way to copy the formula, of course, is to double click the Fill Handle; scroll down a little bit and we will see that sure enough, we have our totals for Central, East and West. Now, be good at this point to make sure that we have all of our regions, nothing is misspelled, if you notice that if we selected three cells, we can look down the status bar in the lower right hand corner and see the sum of those cells is 530,746. If I select all the cells in the revenue column, I should have the exact same total. I will click on G46, again, Shift and Up and sure enough in the lower right hand corner, the sum of those cells is 530,746. I start to have a pretty good feeling that my SUMIF is working and I have captured all of the revenue in the table.

Now, let us say that we want to edit this table and instead of revenue summary by region, we want to create a revenue summary by product. I will copy my formula down from C49 to C54; Control+C to copy, Control+V to paste. Of course we get completely the wrong answer because there are no regions that say ABC. We are going to have to edit this formula to look at a different criteria range. T