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.

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.

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

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.

34 Comments

  1. amy zhang on May 7, 2013 at 7:47 pm

    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.

    • jefflenning on May 7, 2013 at 10:18 pm

      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

  2. amy zhang on May 9, 2013 at 8:04 pm

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

  3. Annika Schmelzle on October 11, 2014 at 8:40 am

    Hi,
    you mention that SUMIFS can be used horizontally as well as vertically. But can you combine both directions in the same formula?

    • jefflenning on October 14, 2014 at 11:42 am

      To my knowledge, the SUMIFS function doesn’t support that type of situation….sorry about that!
      Thanks
      Jeff

  4. Ann on July 9, 2015 at 12:15 pm

    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

    • jefflenning on July 13, 2015 at 2:38 pm

      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

  5. Lisa Croyle on August 29, 2015 at 9:43 am

    Yeah, SUMIFS make my head spin. Powerful stuff.

  6. Sean Cody on March 31, 2016 at 3:11 am

    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?

    • jefflenning on March 31, 2016 at 3:42 am

      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

  7. Dev Maharaj on May 20, 2016 at 2:28 am

    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"

    • Kurt LeBlanc on July 1, 2016 at 9:25 am

      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

      • dev on July 4, 2016 at 12:21 am

        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

  8. Aurdrea Weiman on February 17, 2017 at 11:25 am

    This function is kinda cool. This is easier than filtering or removing duplicates. Thanks!

  9. Robin Gore on March 6, 2017 at 12:01 pm

    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!

    • Jeff Lenning on March 6, 2017 at 12:03 pm

      Awesome…glad it helped…SUMIFS is one of my favorites 🙂

  10. Simon A on April 20, 2017 at 4:04 am

    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

    • Jeff Lenning on April 20, 2017 at 5:22 pm

      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.

  11. Anita on June 16, 2017 at 3:02 pm

    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

    • Jeff Lenning on June 17, 2017 at 4:11 am

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

  12. cynthia osborne on June 27, 2017 at 6:49 pm

    This will be very helpful in the monthly tax reports

  13. Carmen Caloian on August 2, 2017 at 8:38 am

    Hi Jeff,

    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.

    Thank you,
    Carmen

  14. Haris on August 15, 2017 at 7:54 pm

    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

    • Jeff Lenning on August 16, 2017 at 6:53 pm

      I’m unaware of how to accomplish that, hopefully someone can help us out.
      Thanks
      Jeff

  15. Gennelee Limpangog on May 9, 2018 at 2:03 am

    Hi Sir,

    How to formula if for a certain column, I only want to sum up the values which font is color red?

    Badly need it.

    Thanks

    • Kurt LeBlanc on May 20, 2018 at 11:23 am

      Hi Gennelee!

      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:
      https://www.excel-university.com/sum-by-color/

      Let me know if that helps!

      Thank you,
      Kurt

  16. Cherie Anderson on January 18, 2020 at 2:14 pm

    I love how you break this down and made it so easy to understand. Thank you!

  17. Jasmine on December 10, 2020 at 2:39 pm

    Started using SUMIFS and SUMIF about 2 years ago, and it has just changed everything for me. It has made using Excel 10x better.

  18. DEE on December 24, 2020 at 12:41 pm

    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.

  19. Annelies Van Damme on November 19, 2021 at 4:11 am

    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!

    • Jeff Lenning on November 19, 2021 at 8:40 am

      Awesome, glad it was helpful 🙂

  20. Vikram Singh Bisht on January 20, 2022 at 6:08 am

    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?

  21. Lena Bash on February 9, 2023 at 10:56 am

    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!

  22. Douglas Hayes on November 30, 2023 at 9:10 am

    I really appreciate the ‘plural-izing’ of these functions –
    why not use the ‘one-function-to-rule-them-all-of-the-same-category’ ?!

Leave a Comment