Here are the differences and similarities in using the SUMIF & SUMIFS functions in Excel:
You can download the file here to follow along. If you get a preview, look for the download arrow in they upper right hand corner.
SUMIF & SUMIFS are great tools for adding up items based on certain criteria. Here is the syntax for each:
Besides the obvious fact that SUMIFS provides for more than one criteria, in the SUMIF function, the data that you are summing is added at the end, while in SUMIFS, that’s defined up front. For this reason, I think that SUMIFS is more logical to use, even for one criteria, since it is structured as the name suggests, SUM first, then the IFS.
Here we have a table with our sales for several salesmen over 6 months and whether they were over or under their targets:
Using the SUMIF function, I want to determine what Tom’s sales were. Since we are using a table, the data ranges in our formula are defined by the heading names. So, the formula for summing the sales for Tom is:
First we selected the criteria range (Salesman), then the criteria (“Tom” as indicated in cell A1), and finally the summing range (Sales), and we get our answer 247,331. To double check, we’ll filter our table using the Table Slicers (You can find the post regarding Table Slicers here) and we get the same answer:
Now, let’s use the SUMIFS function to determine Bob’s Sales for March and just when he was Under target. The formula for that will be:
For SUMIFS, first we select the Sum Range (Sales), then our first criteria range (Salesman) and that criteria (Bob in A3), then our second criteria range (Month) and that criteria (Mar in B3), and finally our third criteria range (Target) and that criteria (Under), and we come up with 18,499, confirmed by the filtered table:
You can use the SUMIFS function even if you only have one criteria, and many feel that’s practical, since, as mentioned above, the structure of the function is more logical. To use the SUMIFS function for our first example, the formula would be:
And, as you can see, the results are the same as when we used SUMIF:
There you have it. SUMIF vs. SUMIFS. I hoped it was valuable to you and please feel free to drop a comment below.