The SUMIFS function is for sure, without a doubt, one of my most favorite Excel functions of all time. I’m sure I use it in well over half of my workbooks, and it is a must-know function for Excel users. I’ll try to get you warmed up, but just know that this post only gets you started. The amount of flexibility, utility, and application of this function goes far beyond this post.
Officially, this function is known as a conditional summing function. It is similar to the SUM function because it adds stuff up. But, it allows us to specify conditions for what to include in the result. For example, add up the amount column of numbers, but, only include those rows where the account is equal to travel. Or, add up the quantity column of numbers, but only include those rows where the sku is equal to A500. These single condition examples could have easily been handled with the SUMIF function, which has been around for years. However, multiple condition summing with the SUMIFS function was first introduced in Excel 2007. You see, SUMIFS ends with an S, and is like the plural of SUMIF. SUMIF handles a single condition, SUMIFS handles up to 127 conditions. That is multiple conditional summing baby.
An example of multiple condition summing is: add up the amount column, but only include those rows where the department is equal to finance and where the region is equal to north. This function also supports single condition statements, so I use it as a replacement for the SUMIF function, and I no longer use SUMIF. I use SUMIFS instead because I know I can easily modify it if additional conditions come up over time.
Let’s take a look at the function’s syntax, and then I’ll show a quick example.
Syntax
The function has the following syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, …)
Where:
- sum_range is the range of numbers to add
- criteria_range1 is the range that contains the values to evaluate
- criteria1 is the value that criteria_range1 must meet to be included in the sum
- … up to 127 total criteria_range/criteria pairs are allowed
The function’s first argument is the range of numbers to add. For example, the amount column. If the amount column is in range A1:A100, then it would look like this:
=SUMIFS(A1:A100, …)
The remaining arguments come in pairs. First, the criteria range and then the criteria value.
So, as you write the formula, it may sound like this: add up the amount column, but only include those rows where the department column is equal to finance. The argument pair for the condition is the range that represents the department column and then the criteria value finance. If the department column is in range B1:B100, then the function would look like this:
=SUMIFS(A1:A100, B1:B100, “finance”)
Although, I really do not like hard-coding function arguments in the function as I did above with the “finance” argument. This, in my opinion, is poor design. These values should instead be placed in cells and then referenced in the function. So, if the finance value was stored in cell C1, then, the function would look like this:
=SUMIFS(A1:A100, B1:B100, C1)
If needed, additional criteria argument pairs can be added to perform multiple condition sums. Let’s do an example. Let’s pretend that the amount data is stored in D21:D44, that the Region data is stored in B21:B44, and the Department data is stored in C21:C44.
Now, let’s say that we wanted to build a small summary report. We could place the region and dept labels into some cells, and then use a SUMIFS function to aggregate the totals based on the data.
The following screenshot provides the idea.
The formula used in D10, and filled down through D15 is:
=SUMIFS($D$21:$D$44,$B$21:$B$44,B10,$C$21:$C$44,C10)
Where:
- $D$21:$D$44 is the column of numbers to add, the amount column
- $B$21:$B$44 is the first criteria range, the region column
- B10 is the first criteria value, the region
- $C$21:$C$44 is the second criteria range, the department column
- C10 is the second criteria value, the dept
This is a pretty simple example, I know, but hopefully it clearly demonstrates how the function arguments are set up. Understanding them is the key to using this function.
Feel free to check out the sample Excel fileif it will be useful.
This function is truly a gift from Microsoft, and I hope you find it as useful as I have…thanks!
Additional notes
We only covered the basics in this post. It is important to note that this function works horizontally as well as vertically, supports wildcards and comparison operators, returns a 0 when no matching values meet the condition, doesn’t get hung up between text and numeric value differences, and uses AND logic for all conditions. This is truly a remarkable function.
