How to Use the SUMIF Command in Microsoft Excel 2007

Well, you know, Microsoft actually listens and they hear the pain that customers are having and this pain with how to do two conditions in SUMIF is one that finally reached the top levels of the Excel development team and they said, “We have to come up with some solution for this.” And so, in Excel 2007, Microsoft has given us three new functions that allow us to specifically solve this problem. Now it is really kind of subtle although you have to appreciate the way that named us the new function. Instead of being called SUMIF, it is called SUMIFS; plural. They added an S to the end because we can have multiple IF conditions in the function.

When we talk about SUMIFS right now, they have also added a new function called COUNTIFS to match the COUNTIF function and two brand new functions, the AVERAGEIF function and the AVERAGEIFS function. Now, between you and me, I think AVERAGEIF is really kind of crazy because I could always do a SUMIF divided by COUNTIF, I could get the average. Well, I guess they figured that was too hard for some people, so they added AVERAGEIF and AVERAGEIFS.

Let us figure out how to solve the problem of conditionally summing based on more than on condition using the brand new SUMIFS function. I will type the equal sign. Start to type SUMIFS, open parenthesis and Excel tells us the first thing we need now is the sum range. This is completely backwards from SUMIF where in SUMIF the sum range was the last field that we enter. But here and you understand why, we have to specify the sum range first; well that our revenue field from G2 to G46. Again, I am going to click in G46. Hold down the Shift key and Up arrow and the Down Arrow to select that range. As I copy this formula, I want to make sure that I am always pointing to G2 to G46, so I will hit the F4 key. Put the comma, next, Excel says we want to find out what criteria range 1 is. Criteria range one in this case is going to be our list of regions. This time I can see A2, so I will click in A2, Shift key and Down to select the rest of my regions and again the F4 key.

Next is the criteria. This is the region that we are looking for right? Up arrow key to move to the Central Region, put on a comma. Next, Excel is looking for criteria range 2 and you will notice that in the tool tip, this is in square brackets, which means that criteria range 2 and criteria 2 are completely optional. Now, of course, I am not sure why you would be using SUMIFS if you only had one criteria range. But as you look further on in the function, the criteria range 3, criteria range 4, criteria range 5 are all optional. You can really stop after any pair of criteria range in criteria.

Our next criteria range is the product column that will be from B2 to B46; because in my screen I can see B46 that is where I click first. Hold down the Shift key and Up and then down to select B2 to B46. Hit the F4 key to put the absolute dollar signs in and then the comma. Now this time I do want to select the product which is to the left of the current cells so I will hit the Left Arrow to go over and select cell B60. At this point, I have entered enough data for this function to actually work so I will hit the closing parenthesis.

Now it is interesting to note at this example, I am summing based on two conditions but Microsoft went completely overboard on this and they now allows to sum by over 50 conditions. Choose Ctrl+Enter and we have the total of sales to just the Central Region ABC. Now I will copy this formula and paste it to the entire table. Okay now, when I pasted that value, I didn’t get the result. Let us take a look back in our original formula to see exactly what is going wrong. I will select the original cell and hit the F2 key to edit the formula. And this is a beautiful thing when you have a formula that is wrong, Excel actually lights everything up in color and so you can see how things are working. For example, G2 to G46 is in blue in my formula and that range in column G2 to G46 is highlighted in blue.

So we need to go through and figure out where this formula went wrong. It i