Excel SUMIFS Sum With Multiple Criteria

In Excel 2007 and Excel 2010, you can use the new SUMIFS function to sum items using multiple criteria.

For example, sum the orders where an account status is Active, and the number of visits is greater than or equal to 10. You can type in the criteria, but it’s better to use cell references, where possible.

Sum02b

I’ve updated the Excel SUM page on the Contextures website, to include the SUMIFS function example and video.

Watch the SUMIFS Video

To see the steps for creating a SUMIFS formula, and using cell reference, you can watch this short Excel video tutorial.

Or watch on YouTube: Excel SUMIFS Sum With Multiple Criteria

_____________

You may also like...

21 Responses

  1. Kevin says:

    I am always amazed that no one ever uses the data functions of excel (any version)

    Table name “DATA” = A1.C6
    Criteria =
    STATUS=”Active” Visits >=10(place in E1.F2)

    =DSUM(data,3,E1:F2) = 325

  2. Stuart Valentine says:

    Great blog.

    On this subject, I believe in some builds of Excel 2010, there is a bug in the SUMIFS formula, whereby if your criteria arrays are spread over different sheets, the formula may evaluate to zero or indeed to just a plain wrong answer.

    I think (not sure) – that this issue has been fixed by an update or service pack from Microsoft, but for users who have not had it applied they should be aware….

    • Charlie says:

      Hi Stuart:
      Not sure if my issue is related to this bug but here’s my formula and excel is not returning any data. I know that there are records on the file that have amounts > 100K.(in the H cells) Maybe you can figure out if I have something wrong? Thanks a lot, Charlie

      =SUMIFS(Data!$H$3:$H$34710,Data!$H$3:$H$34710, “>100000″,Data!$AB$3:$AB$34710,Summary!E$3,Data!$AA$3:$AA$34710,Summary!$B$68,Data!$AC$3:$AC$34710,Summary!$C69,Data!$U$3:$U$34710,”*intergov*”)

  3. Kevin says:

    Hard coding criteria such as “>=10” is never a good idea when building a spreadsheet.

    1) It relies on “good” formula without the ability to change criteria on the fly. Business models constantly “change”.
    2) See Chandoo’s spreadsheet risk links. Bad formula’s abound (http://chandoo.org/wp/2011/12/07/spreadsheet-risk-management-introduction/)

  4. SXM says:

    1 1/29/2012 2/25/2012
    Date Quantity Price
    1/29/2012 0 4 
    1/30/2012 2 7 
    1/31/2012 1 9 
    2/1/2012 1 16 

    So the the number “1” is a drop down (1 to 12) , when I change the from “1” to “2”, the starting and ending date changes, so if I select 2, the starting date would changed from 1/29/12 to 2/26/12, and ending date will change from 2/25/12 to 3/31/12, the dates are +1 from starting date, the quantity and price are vlookup based on date, there are 35 set lines (coz that is the max it can have, but I want only the ones to sum with the start and end date. So say even if it there is something on 2/26, when I have 1 selected I want it to sum only till 2/25 based on the last date.

  5. Ed says:

    =SUMIFS($D$2:$D$605,$A$2:$A$605,”>=”&$F660,$A$2:$A$605,”<="&G660,$B$2:$B$605,OR(C660,K660))
    This formula worked until I tried to do an OR at the end to include K660. Do I just have bad syntax or can it not be done?

  6. Ron says:

    Can the same criteria range be used more than once? All the examples I have seen refer to two separate criteria ranges such as “Status” and “Visits” columns your example shows.

    I have two columns (sum range = D3:D17,and criteria range = C3:C17). The first formula listed returns 0 as the value. Intent is to add the values in Column D if Column C is equal to a 20 or a 50.

    =SUMIFS(D3:D17,C3:C17,20,C3:C17,50)

    If I eliminate the second criteria, the formula works and returns the sum of the items with 20 in the criteria range; but isn’t any improvement over SUMIF.

    =SUMIFS(D3:D17,C3:C17,20)

    If I add two SUMIF formulas together, I get the correct value.

    =SUMIF(C3:C17,20,D3:D17)+SUMIF(C3:C17,50,D3:D17)

    Any clue why the first wouldn’t work? I’m using Excel 2007.

  7. Peter Eden says:

    Ron,
    The first one will not work as it is looking at two criteria to be true, and no cell in C3:C17 can be two values at the same time !

    Peter

  8. Juanita says:

    Why Does Sumifs work with operators of Less Than or Greater than, but not exact numbers? Example

    =SUMIFS(D3:D17,C3:C17,20,C3:C17,50)
    This will not work, but

    =SUMIFS(D3:D17,C3:C17,”>” & 20,C3:C17,”<" & 50)
    This will work.

    • Terry P. says:

      Juanita,

      If you use “<50" (or whatever the number you are testing against) in the SUMIFS, I have gotten that to work. I don't know what has been changed, but I seem to get sporadic results using a list {value1, vaue2, …} in hte criteria.

  9. Teflon.S says:

    @Juanita,

    SUMIFS works for exact numbers:

    =SUMIFS(D3:D17,C3:C17,”=20″,D3:D17,”=40″)

    Your first example works because it is summing values in D where column C contains a value between 20 and 30 (>20 AND <50). Your second example is summing values where Column C contains 20 AND 50. As Pete says above, this can never happen.

  10. Arslan Tariq says:

    Approved Formulas:

    =SUMIFS(H2:H17,A2:A17, “>=19999”,A2:A17, “<=30000")
    =SUMIFS(Sheet1!H2:Sheet1!H17,Sheet1!A2:Sheet1!A17, A2)

    Optional Formulas:

    =VLOOKUP(A2,Sheet1!$I:$J,2,FALSE)
    =SUMIFS(H2:H17,A2:A17, "a")

  11. srikanth says:

    good one, it helped me a lot

  12. Suzie.M says:

    I’m trying to use SUMIF formula with two different sheets (both are in same file) however I keep getting zero as answer. HELP! Example =sumif(‘filename’!$D$2:$D$100,A10,$G$4:$G$100) is there something that I’m missing so formula will get data from other sheet? THANKS!

  13. Cindy says:

    Can you use sumifs when your criteria is or, vs. and? If so how should this be written?

  14. Susan says:

    This returns a value:

    =SUMIFS(‘Program ‘!$X:$X,’Program ‘!$V:$V,”=” & F3)

    But this does not return a value, only 0

    =SUMIFS(‘Program ‘!$X:$X,’Program ‘!$V:$V,”>=” & F3)

    Any idea why?

  15. Avik says:

    Hi,

    I am using =SUMIFS(Q15:Q155,P15:P155,”2014″,O15:O155,”Canada”), but it is returning “0” please healp me to get expected result.

  1. August 5, 2013

    […] Can you please refer the below links. Excel SUMIFS Sum With Multiple Criteria | Contextures Blog SUMIFS function – Excel – Office.com The problem with second criteria please correct the formula […]

Leave a Reply

Your email address will not be published. Required fields are marked *