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 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!

## 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.

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.

Amy,

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!

Thanks

Jeff

it certainly does! thank you so much for the quick response Jeff.

Hi,

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!

Thanks

Jeff

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

Ann,

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!

Thanks,

Jeff

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.

Thanks

Jeff

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.

Cheers,

dev"

Hey Dev!

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!

Kurt LeBlanc

Hi Kurt,

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 ?

Regards,

dev

Hey Dev,

That may give you the right answer, but probably just once…Could you send me your file or copy the table into a comment to help me visualize it and explain the results you need? My email is kurleb10@gmail.com

I’m happy to help:)

Kurt LeBlanc

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?

Many thanks

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.

Hello,

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

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: http://www.excel-university.com/moving-rolling-and-trailing-averages/

Hope it helps!

Thanks

Jeff

This will be very helpful in the monthly tax reports