Multiple Condition Summing in Excel with SUMIFS
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.
The function has the following syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, …)
- 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:
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:
- $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 file if it will be useful.
This function is truly a gift from Microsoft, and I hope you find it as useful as I have…thanks!
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.
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.
I am curious to know how to set up the criteria in other ways. For example, I only want the sum of data where such data is higher than $400. Also, how did you figure out the different combination of Region or Department to feed into the report section? In your example, it’s relatively easy to do it manually, but in real life, there may be more regions (branches), and more departments choices, could you share how to automate that?
I remember as a warm-up for the Excel University, you showed us how it was done, but it was so fast (impressive!) that I wasn’t able to follow it.
Hi there! Regarding how to set up conditions so that they use a comparison operator, such as greater than, you just enclose the comparison operator in quotes and then use the concatenation operator (&) to join the comparison operator to the cell reference. It will make more sense to see an example. Using your question, how to include data higher than $400, it would be this:
=SUMIFS(amount_col, criteria_range1, “>”&400)
Or, if 400 was stored in cell A1:
=SUMIFS(amount_col, criteria_range1, “>”&A1)
Regarding how to quickly generate a unique list of the regions and departments, I use the Remove Duplicates feature. The steps are to copy the entire data label column, full of all of the duplicate department names, and then paste. Then, on the pasted range, I use the Remove Duplicates feature to leave me with a list of the unique values. Then, I use that unique list as the basis for my report.
If there were new items added only occasionally, this approach is terrific. If there are new items added frequently, then I may lean towards a PivotTable instead of a formula based report.
Hope this info is what you were looking for, and hope it helps…thanks!
it certainly does! thank you so much for the quick response Jeff.
you mention that SUMIFS can be used horizontally as well as vertically. But can you combine both directions in the same formula?
To my knowledge, the SUMIFS function doesn’t support that type of situation….sorry about that!
I need a formula that will sum a column range based on 3 sets of 2 criteria in ranges. ie:
A B C Results
Cotton 2015 $500 Cotton 2014 $0
Wheat 2014 $400 Cotton 2015 $500
SoyBeans 2015 $300 Wheat 2014 $600
Wheat 2014 $200 etc
Thanks so much
Doing this should be no problem with the SUMIFS function as it can perform multiple condition summing. You basically pass the sum column as the first argument, such as C:C. Then, the remaining arguments come in pairs, the criteria range and the criteria value, such as A:A, D1 and B:B, E1. For example, =SUMIFS(C:C, A:A, D1, B:B, E1).
Hope this helps!
Yeah, SUMIFS make my head spin. Powerful stuff.
I’ve recently seen the light and come under the spell of SUMIFS – magnificent!
Problem is I’m now finding its taking its toll in terms of file size.
In summary, I get a 10 meg spreadsheet to analyse every week with 64000 odd line items. In the past I added Pivots (which I hate) and muddled through. SUMIFS cleared the way for condition based standard reports & charts which gave me all I needed with a simple data refresh. However, every new tab / report costs me 3 meg+ in file size. I’m now at 24m meg and re-calcuation can take 10 seconds or more. Is this the hidden cost of SUMIFS or am I executing incorrectly?
Sean…To my knowledge, SUMIFS shouldn’t have a hidden cost or artificially inflate file size or hurt recalc time. I would start troubleshooting by confirming that the SUMIFS isn’t being used in an array formula and that the file type is xlsx and not xls.
I have a query on the Sumifs function within excel and hope that you can help. I will illustrate by a simple example below.
Column A Column B
Row Account No Amount
1 212200 10
2 212300 5
3 218000 2
4 214000 20
5 215000 5
6 217000 8
Sum range = column B (B1:B6)
Criteria Range = column a (A1:A6)
Criteria : >=212200, <=217000 but exclude 214000 to 215000. The answer should be 23.
How do I set this up in a formuale to give me the correct answer.
I looking forward to hearing from you.
This can be accomplished with SUMIFS() with concatenation:)
You will have to break it into several conditions so take your time setting it up.
You’ll add the cells in the column where the amount is above the begging range and up to your first criteria and where its above the second criteria and up to the end range. Let me know if you need additional help setting it up.
Hope this helps!
Thank you for your reply…Please confirm whether you had this is mind:
=SUMIFS(B1:B6,A1:A6,”>=212200″,A1:A6,”214000″,A1:A6,”217000″). It gives me the required answer (23).
I had initially set up my formulae as above. However, it can be a long process especially if the range is large and has a number of exclusions.
I am looking for formulae to do this calculation more simply i.e simply specify the full range and then the exclusions. Is there such a formulae in excel ?
This function is kinda cool. This is easier than filtering or removing duplicates. Thanks!
I read through this post and immediately thought of the perfect use of SUMIFS in an existing workbook that I update monthly. I’ve already updated my worksheet to include the appropriate SUMIFS functions. Thanks so much!
Awesome…glad it helped…SUMIFS is one of my favorites 🙂
I am struggling to get this to work where I am comparing text values.
I am trying to sum a numerical figure, when the criteria ranges are both textual representations of numbers. If I directly compare the two, I get a TRUE result, so would expect SUMIFS to use similar logic
EG A10 = 4000 (but as text), b10 = 3900 (as text), C10 = 4100 (as text) and d1:d10 are numbers (proven by isnumber(D10) resulting in TRUE)
If I enter A10>b10, I get TRUE.
If I enter A10 =”&a10,c1:c10,”<="&a10) I get zero as a result
Do you know of a method of getting this to work, please?
SUMIFS is a remarkable function, and equivalent values, even if stored as different data types, will match. This is different from traditional lookup functions such as VLOOKUP. So, if I understand the set up, I think you should be able to reference the cell values directly. I may not have understood the structure of the worksheet correctly, and if not, sorry about that. But comment back as needed, I’m happy to help.
I have a query on the Sumifs function. Appreciate if you can help.
I have forecast data in weeks.
Week 1, Week 2, Week 3, etc.
100, 200, 300,
If I’m told to sum up forecast data for 3 weeks, it should sum up 3 weeks demand. If it says 4 weeks, it should sum up 4 weeks forecast data and so on.
I think i need to use sumif. But don’t know how to proceed. Since the range is dynamic.
thanks in advance
Anita, there area few options for something like that. If you use SUMIFS then you’d want to use a comparison operator such as > and < to define the range based on month number. Alternatively, you could use OFFSET or INDEX and I have a blog post that demonstrates how: https://www.excel-university.com/moving-rolling-and-trailing-averages/
Hope it helps!
This will be very helpful in the monthly tax reports
I’m trying to add information from multiple tabs to one. What function do I have to use ?
I have 24 tabs with time entry by projects/employee and I need to get the total for each project at the end of year.
Hi jeff, i want to ask some solution for my problem. how to solve this. i want to combine , how to write if the sum range must be horizontally, condition 1 also horizontally but for condition 2 its take vertically. hope you can help me .. thanks
sorry my bad english
I’m unaware of how to accomplish that, hopefully someone can help us out.
How to formula if for a certain column, I only want to sum up the values which font is color red?
Badly need it.
The formulas in Excel only work with the underlying data, but there is a way to work around this “limitation.” Here’s a blog that can help walk you through it:
Let me know if that helps!
I love how you break this down and made it so easy to understand. Thank you!
Started using SUMIFS and SUMIF about 2 years ago, and it has just changed everything for me. It has made using Excel 10x better.
I need a sumifs formula for three conditions: bring in sum of column t if in column p, $F$9 is found, then in column q, $h9 is found and in column s, $E9 is found. I tired sumifs (T;T, P:P,$F$9,Q:Q,$H9,S:S,$E9) but it will not work.
Thanks so much for the clear explanation – my brain was able to follow the reasoning (even though it has a tendency to struggle with abstract concepts), and this worked brilliantly in a file I was struggling with!
Awesome, glad it was helpful 🙂
Hi can you please help on this,
Suppose i have 50000/- value in one cell and now i want to split this amount in 10 different cells, the sum of 10 different cells value should be equal to 50000/-.
Is there any way to do this?
I have so many worksheets that utilize this function. I didn’t write the formulas so I look forward to better understanding what the formula as written with this instruction. Thanks!