Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

Sumif with multiple conditions [quick tip]

Posted on July 22nd, 2009 in Excel Howtos - 68 comments

Here is a little formula trick if you need to sum a range of cells based on multiple conditions.

Assuming you have the starfleet, captain and flight data like this (seriously, what are you doing with excel again?)

Sumif with multiple conditions - data

In order to findout how many flights James Kirk did on the Amarillo, you can write an array formula like this:
=SUM(($D$3:$D$24)*($B$3:$B$24="Amarillo")*($C$3:$C$24="James Kirk"))

Sumif with Multiple ConditionsRemember, the above is an array formula. So you need to type it and press CTRL+SHIFT+Enter.

Update: Or you can also use SUMPRODUCT (replace SUM in the above formula with SUMPRODUCT and just hit Enter (Thanks to Simon for teaching me this tip)

As you can guess, column B has ship names, C has captains and D has # of flights.

Using the same logic, you can make a cross tab (or pivot) like the one shown on the right too.

Further Resources:

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

68 Responses to “Sumif with multiple conditions [quick tip]”

  1. Simon says:

    Could you not just use the sumproduct function in the exact same way, yet with more flexibilty as you are avoiding using it as an array?

    ie ( grain of salt on table location, please :) ) =SUMPRODUCT(($A$1:$A$7=$F3)*($B$1:$B$7=G$1)*($C$1:$C$7))

  2. Worm says:

    I would normally do this with SUMPRODUCT

    Is there any advantage to using arrays?

  3. Chandoo says:

    @Simon.. yeah. Very good point. I didnt know SUMPRODUCT could work like this. Donut for you.. :)

    @Worm… I am not sure if there would be some advantages of using arrays. May be when you combine the sum with some other formulas in a model, array formula might be better than the normal version. But I dont know any good examples of that. May be one of the more experienced readers can tell me.

  4. Kapil Kithani says:

    Hi, I am fairly new to this site and I really thank Chandoo for maintaining this site, but trust me on Sum Product, It is THE best formula that I have ever come across.

    For Eg you can replace a PIVOT table using sum product. Yeah I see a lot of frowns, but SUM Product equally works on numbers as well as on text.

    For Text, use =sumproduct(–(Range1=”abc”),–(Range2=”XYZ”)) where range1 is the range and abc is the condition in that range
    In this, excel would return an array in the 1st range(1s and 0s for matching abc in the Range1), and return an array in the second range(1s and 0s for matching XYZ in the Range2), and voila, you have the data. Just make sure that the start and end points of Range1 and Range2 are same. (Length of both the range should be exactly same or else you get a #VALUE error.

    As for Numbers, just remove the 2 hyphens before opening the brackets and remove the condition…. ! :) and additionally, you can mix text and numbers and remove hyphens for any ranges having only numbers(it would return the count of occurence)

    Hope you got the point….. I am all for Sum product..

    One word of caution though..Since this returns and array for every condition, if might get heavy on the excelsheet.

  5. Ken Puls says:

    Hi Chandoo,

    In Excel 2007 you can also use the new SUMIFS function to sum with multiple conditions. The only rub is that it isn’t backwards compatible like the array or SUMPRODUCT solutions.

  6. Martin says:

    You guys are fantastic !!! I’m finding easier each time to work with named ranges !
    Here’s my little contribution:
    Named Ranges (should be dynamic, but….)
    Ship $A$2:$A$8
    Captain $B$2:$B$8
    flights $C$2:$C$8
    in F:F
    Summary_ship $F$2:$I$2
    this 3:3
    Summary_Captain $E$3:$E$6

    =SUMPRODUCT((Ship=in Summary_ship)*(Captain=this Summary_Captain)*(flights))

    data is in range A1:C8, and summary is in E1:I6.

    rgds,

    Martín

  7. Mathias says:

    I like that the 8th starship flight on your list has been pixellated. Is this the secret black-ops flight to Titan, and did the Intergalactic Intelligence Agency pressure you to keep that info under wraps?

  8. Cheryl says:

    Kpil, would you explain what the hyphens are for please? Or is it only to indicate text rather then numbers?
    Thank you.

  9. Cheryl says:

    Kapil, would you explain what the hyphens are for please? Or is it only to indicate text rather then numbers?
    Thank you.

  10. derek says:

    Cheryl, the output of an expression of the form X=Y, in Excel, is one of the logical values TRUE or FALSE. These can often be evaluated as 1 or 0, but SUMPRODUCT needs a little clue first. Typing in two negative signs provides the clue by turning the logcal values into numerical values (negating and then negating again leaves the values unchanged, but now they’re obviously numbers and not Boolean entities).

    There is an alternative to two minus signs, and that’s the N() function, which evaluates anything inside the brackets as a number. But there’s no obvious advantage to using N(), and most Excel users don’t bother.

  11. Kapil Kithani says:

    Cheryl : the 2 hypens make sumproduct return an array, and multiple conditions then have multiple arrays…

    Once you have the result in arrays then a sumif is performed internally and result is displayed..

    I hope this helps…

  12. Kapil Kithani says:

    Rather put it simply, it converts the SUMPRODUCT into an array formula (something similar to CTRL+SHIFT+ENTER thing that many ppl do to convert a formula into array formula… )

    For More on Arrays, Chandoo and his wonderful site can explain in a much subtle and simpler way :)

    Derek: I am sorry, but I did not understand your post. When the two hypens are used, it ask sumproduct to return the output in the form of an array (something similar to CTRL+SHIFT+ENTER). Please correct me if I am wrong, since I use this day in and day out but i never encountered a problem.. :)

  13. Worm says:

    Derek was correct.

    The two ‘hyphens’ are simply two negative symbols, turning TRUE or FALSE into 1 or 0, and using the * between the arrays in Simon’s example does the same thing.

    I use the — method because it looks clearer to me, but both work.

    My SUMPRODUCT formulas end up like :

    =SUMPRODUCT(–(Section=”Management”),–(Salary=”Too High”))

    the other way is

    =SUMPRODUCT((Section=”Management”)*(Salary=”Too High”))

    horses for course really…

  14. Worm says:

    silly formatting changed two – into –
    should be – - ;)

  15. Cheryl says:

    Thanks! very very helpful :0)

  16. Chandoo says:

    @Ken: Thanks, I am aware of the IFS functions in excel 2007. Since they are not backward compatible, I thought recommending them would be unwise.

    @Kapil, Worm and Derek.. that is so cool. I have always seen – - being used in formulas and wondered what it meant. Now I know :)

    @Mathias: Hush… !

    @Martin: Very cool.. thanks :)

    @Cheryl: You are welcome.

  17. skrell says:

    i’m sorry if this is a stupid question, but why do you use ‘$’ in the function? Isn’t ($D$3:$D$24) the same thing as writing (D3:D24) meaning the “range of values from D3 block to D24 block”? Is it because you are working with arrays?

  18. Worm says:

    The $ makes an absolute reference that doesn’t change if you copy the formula to another cell.

    If you copy a formula with ($D$3:$D$24) from A1 to B1 it stays the same, but if you copy a formula with (D3:D24) it becomes (E3:E24) – this is probably not going to be what you want – although it might be – depends on circumstances (as always … :) )

    This is why using named ranges can be useful as it makes it clearer what is being done.

    (IMHO of course…)

  19. Steven says:

    I am using arrays in a spreadsheet ,but have found them to be very slow. Will my problems be solved if I replace them with SUMPRODUCT?

  20. skrell says:

    oh! thanks! I learned something today!

  21. Chandoo says:

    @Steven… It is true that arrays make your spreadsheet slow. But only if the array formula is applied over a large range or is processing lot of cells. But I am sure even sumproduct will become slow when you paste it over thousands of cells.

    My suggestion is use sumproduct if you can. It is cleaner and simpler to use. It is easy to explain and change. If the formulas are dragging your spreadsheet, then you may have to change the calculation options to manual and press F9 before taking a coffee break.

  22. Steven says:

    Thanks Chandoo – you have a great site !

  23. Manoj Tiwari says:

    Very helpful?
    Please solve the following situation in Excel:
    A2:A10 is different fruits name
    B2:B10 contains either day or night
    C2:C10 contains contents in number
    Now,
    Search “Apple” from A2:A10,
    search “Day” from B2:B10 and
    sum all the numbers (c2:c10)meeting the above criteria

  24. Chandoo says:

    @Manoj: Are you not able to use sumproduct for the above?

  25. Worm says:

    Indeed. Perfect case for SUMPRODUCT

    =SUMPRODUCT(–(A2:A10=”Apple”),–(B2:B10=”Day”),C2:C10)
    or
    =SUMPRODUCT((A2:A10=”Apple”)*(B2:B10=”Day”)*(C2:C10))

    Using named ranges would make it even clearer…

  26. [...] Assuming “log_member_names” refers to the member name column and log_weeknum refers to the last column in the timesheet, we can write a simple SUMPRODUCT formula like this: =SUMPRODUCT(–(log_member_names="John Galt"),–(log_weeknums="3")) to calculate the count of activities John Galt has done on week 3. Learn more about using SUM and SUMPRODUCT to calculate sumif with multiple conditions. [...]

  27. Glenn says:

    I am trying to put together a tool that can can search, column by column,
    for a certain background/fill color, using Conditional Formating (Red), and then copy/paste the
    value/text in various columns of the same row for each instance of Red
    background colored cells, into another worksheet.

    Thanks, any help would be greatly appreciated…

  28. arturo says:

    GRADO M H
    1 X
    1 X
    2 X
    2 X
    2 X
    1 X
    1 X
    2 X
    4 X
    6 X
    4 X
    2 X
    1 X
    2 X
    6 X
    6 X
    4 X

    quiero saber cuantas mujeres de primero hay, por separado tambien los hombres, cual sera la funcion.

  29. Martin says:

    for those who do not understand Spanish, what Arturo meant was that from that list, he wants to know how many people by grade (“grado”) and gender (M means Women, H means Men).
    Arturo, deberías trabajar en una tabla dinamica con ese rango, poniendo como filas el campo Grado, y como datos la cuenta de Hombres y la cuenta de Mujeres.

    (Arturo, you should use a Pivot Table here, with the grade field as rows, and the count of Men as data, as well as the count of Women).

    I presume that’s the fastest way, I’ll look into it tomorrow and let you all know. (It’s 12 in the night, my son is crying, and I just got from seeing Iron Man 2, and wanting to go to bed…).

    Rgds,

    Martin

  30. Mehboob says:

    cell a2:a43 contains extension
    cell b2:b43 contains price
    cell c2:c43 contains products name

    i want formula where i can only enter extension and then it should show contains price with products name

    thanks

  31. Hui... says:

    @Mehboob
    Try =INDEX(A2:C43,MATCH(E2,A2:A43,0),3)&” – “&INDEX(A2:C43,MATCH(E2,A2:A43,0),2)
    Put your value to lookup in E2 or change the reference

  32. Rohon says:

    I am trying to edit an array to add another condition. The array is counting events in a specific year, tied to a specific person. I want to add another condition, but the value of the new condition is subject to change. I don’t care what the value is, I just want to count the number of times the value occurs. I have a column for this value, and I want to count the number of times a cell in that column has any text. How do I do that? Thank you.

  33. Hui... says:

    @Rohon, Can you post your formula so we can see what your doing?

  34. Rohon says:

    THANK YOU!!!

    Here is the formula, as is

    {=SUM(IF(‘DH-Starts’!$N$7:’DH-Starts’!$N$511=$F$1,IF(‘DH-Starts’!$M$7:’DH-Starts’!$M$511=”N”,IF(‘DH-Starts’!$H$7:’DH-Starts’!$H$511=B16,1,0))))}

    I want it to do all of that, plus go to the sheet of DH-Starts and count the number of entries in Column G, from row 7 through row 511. I’m not concerned with what is actually IN a cell in Column G, I just care that there’s SOMETHING in that cell — does that make sense?

  35. Felicia says:

    My attempt:

    =SUMPRODUCT((Sheet!B57:Sheet!B69=”Criteria”)*(Sheet!O57:Sheet!O69=”Criteria”)*(Sheet!E57:Sheet!E69=”Critera”),(Sheet!K57:Sheet!K69))+SUMPRODUCT((Sheet!B57:Sheet!B69=”Criteria”)*(Sheet!O57:Sheet!O69=”Criteria”)*(Sheet!E57:Sheet!E69=”Critera”),(Sheet!K57:Sheet!K69))

    I had troubles using *(Sheet!K57:Sheet!K69) and ended up trying ,(Sheet!K57:Sheet!K69) at the end of the formula; this worked for me

  36. A says:

    Hi
    We want a sum of sales details from a file for particular name and particular month. Sales file has name, month and sales volume. we need output in other file for particular name for particular month. sales detail for a particular name in single month might have more than 1 lines. pls provide formulae

  37. Hui... says:

    @A
    Have you looked at using an Advanced Filter?

  38. Sylvain says:

    Couple on questions on the formula above.

    1. Why do we need to multiply arrays in sumproduct formula, isn’t it the point of using sumproduct?
    i.e. why doesn’t this work?
    SUMPRODUCT(($D$3:$D$24),($B$3:$B$24=”Amarillo”),($C$3:$C$24=”James Kirk”))

    2. I would like to use name ranges for my data cells (so I can copy across a wide range of data)
    Let’s say Flights = $D$3:$D$24 , I can write:
    SUMPRODUCT(Flights*($B$3:$B$24=”Amarillo”)*($C$3:$C$24=”James Kirk”))
    But if in my output table I have Flights in cell F3, I thought about:
    SUMPRODUCT(Text(F3,)*($B$3:$B$24=”Amarillo”)*($C$3:$C$24=”James Kirk”))

    but it doesn’t work….

    Thanks in advance for you help guys!

  39. finton says:

    @Sylvain, Where Flights is a named range, try SUMPRODUCT(INDIRECT(F3)*($B$3:$B$24=”Amarillo”)*($C$3:$C$24=”James Kirk”))

  40. Sylvain says:

    Thanks!

  41. Richard says:

    Wow! I get all of this. Chandoo is awesome and so are all of the contributors. Since joining this site people think I’m an excel genius. Thanks to all of you.

  42. Rutto says:

    Thanks Guys for the good work. I have a small problem though. I am attempting to put together students’ grades with some optional subjects in there. Scenario is; Add 3 compulsory subjects, pick best two sciences out of three, select best humanity out of three, choose one technical subject out of three and pick any one other best performed subject from the remaining science or humanities or technicals. I willgreatly appreciate your help on this matter.

  43. Chris says:

    Chandoo – you have provided the answer that I needed after over a full days worth of google searching on how to write a working formula here at work. Thanks for making this easy to follow for normal folk such as myself.

  44. Abot says:

    Hi guys,
    I trying to do a SUMIF from pivot table. The data are in pivot table, names are in colum and values (positive and negatives) are in row. On another sheet I want to sum if the name match, then add negative numbers in one colum and positive in another colum.
    Many thanks

  45. Paul says:

    This formula works amazingly well to find all the instances in column A for the multiple criteria in column C and returns the quantities of the column A items that are in column B.

    =SUMPRODUCT(SUMIF($A$413:$A$419,” * “&$C$413:$C$416&” *”, $B$413:$B$419))

    How can I get this to work in VBA?

  46. Esta says:

    Hi,
    I am trying to figure out how to build a formula, I thought SUMIFS would do the trick, but it seems I need an OR operator.

    here is what I am trying to do. I have a sheet A, that contains Sales Rep Names and Sales Amount. In sheet B, I have another set of names that I need to pair up with Names on Sheet B and sum amounts. In other words
    Sheet A
    Alex – Sales $15
    Michael – Sales $15

    Sheet B
    John – - John’s amount is a combination of Sales of Alex and Michael.
    add Alex + Michael =$25. I have this formula – but it’s really stupid, I am not saving myself any time:) SUMIFS(annual,rsds,”alex”)+SUMIFS(annual,rsds,”michael”)

    Thanks,
    esta

  47. Worm says:

    I think you’ve got the parameters in the wrong order. It’s SUMIF(range,criteria,sum range) – so it’s likely to be SUMIF(annual,”alex”,rsds)+SUMIF(annual,”michael”,rsds)

  48. Mary says:

    I want to use an “or” condition within an array within a sumproduct statement.

    Example of what I have now:
    =sumproduct((a2:a100)*(b2:b100=”Atlanta”)*(c2:c100>=VALUE(“09/01/2011″)))

    What I want: is to make the following work whenever “Houston” OR “Atlanta” appears in column B, not just Atlanta.

    Any suggestions for the syntax?

  49. Worm says:

    You need to add the two conditions.

    SUMPRODUCT((a2:a100)*((b2:b100=”Atlanta”)+(b2:b100=”Houston”))*(c2:c100>=VALUE(“09/01/2011?)))

  50. Sabrina says:

    I am having difficulty with the sumproduct formula
    Here is my formula

    =SUMPRODUCT((’2012′!N:N)*((’2012′!K:K=”AFE”)+(’2012′!K:K=”AFT”))*(’2012′!A:A=’YTD summary’!A5))

    I have multiply conditions and multiple spreadsheets
    What am I doing wrong

    • Worm says:

      You can’t use whole column references in SUMPRODUCT. You have to use a full reference. So ’2012?!N:N won’t work – you need (for instance) ’2012?!N2:N200 and so on.

  51. James says:

    I modified the above formula for summing multiple criterias to pull data from one worksheet into another: =SUM((‘Parts Usage’!$F$2:$F$1691)*(‘Parts Usage’!$A$2:$A$1691=”Jan-11″)*(‘Parts Usage’!$D$2:$D$1691=”MK-2326″))

    However, it only returns a value of 0. Any help to correct my formula would be greatly appreciated.

  52. Hui... says:

    @James
    I think your date is incorrect
    The Date you have displayed is a Formatted Date and is only showing the Month-Year as Jan-11
    where as I think the cells will have real dates

    Select several date cells
    Change the custom number format to dd mmm yy
    if they are all the same probably 1 Jan 11 your formula will need to be:
    =SUM((‘Parts Usage’!$F$2:$F$1691)*(‘Parts Usage’!$A$2:$A$1691=”1-Jan-11?)*(‘Parts Usage’!$D$2:$D$1691=”MK-2326?))

    or

    =SUM((‘Parts Usage’!$F$2:$F$1691)*(‘Parts Usage’!$A$2:$A$1691=Date(2011,1,1))*(‘Parts Usage’!$D$2:$D$1691=”MK-2326?))

    or

    =SUM((‘Parts Usage’!$F$2:$F$1691)*(‘Parts Usage’!$A$2:$A$1691=40544)*(‘Parts Usage’!$D$2:$D$1691=”MK-2326?))

  53. Worm says:

    In general when matching dates, I’ll match against a cell with a date in it. So I would, for instance, put the date ’1 Jan 2011′ in cell T1, then that part of the formula just becomes

    (‘Parts Usage’!$A$2:$A$1691=$T$1)

    and avoids fiddling with formats.

  54. James says:

    I did do this, but it is still giving me problems.  Sorry for not responding sooner to your reply.

  55. Julie says:

    Hello
    I’m using the SUMIF to tally a a column wiht a -number
    what it’s doing is, if in column g there is a c then add up what is in column f
    but in colum f i have a – but it’s still adding not doing a real total.
    how can i fix this
    this is the formula i’m using
    =SUMIF(’218710′!G3:G73,”C”,’218710′!F3:F73)

    • Hui... says:

      Julie

      Do you want to include or exclude the negatives?
      As I’m pretty sure Excel won’t care if the numbers in Column F are +’ve or -’ve

      If you want to exclude the negatives as well try:
      =SUMIFS('218710'!F3:F73,'218710'!G3:G73,"C",'218710'!F3:F73,">0")
      (Requires Excel 2007+)

      or
      =SUMPRODUCT(('218710'!G3:G17="C")*('218710'!F3:F17>0),'218710'!F3:F17)
      (All Excel versions)

      If you copy / paste the above retype the ” and ‘ characters in your formula

  56. Ian says:

    Hi, I’ve been using SUMPRODUCT with dynamic Ranges (as the dataset grows month on month) for a few months and all of a sudden the formulas are returning #N/A…I have no idea why this is. Is this a known issue for SUMPRODUCT?

    The formulas have been working fine up until I’ve just pasted in the new dataset. An example of one of the formulas is as follows:

    =SUMPRODUCT((clientgroup=”physical disability”)*(count=”Yes”)*(temp?=”permanent”)*(ageband<>”18-64″)*(hometype=”local authority staffed”)*(remove=”no”))

  57. Worm says:

    Are all the ranges the same size? They need to be.

  58. Ron Scalf says:

    I’m trying to find the correct formula and not sure if I need sumif or sumif s. Can anyone give me some advice.
     
    Bowler 1 has a score of 240 and is awarded 2 points
    Bowler 2 has a score of 190 and is awarded 1 points
    Bowler 3 has a score of 180 and is awarded 0 points
     
    Total points that can be awarded is 3 points.
    Problem is what happens if there is a tie?
    As of now I have to go in and award the points for this and was trying to get excel to do it for me.
    Bowler 1 has a score of 240 and is awarded 1.5 points
    Bowler 2 has a score of 240 and is awarded 1.5 points
    Bowler 3 has a score of 180 and is awarded 0 points
     
    Below is the break down on how the points are awarded.
     
    Singles: 2 Points for 1st
                1 points for 2nd
                0 points for 3rd
    Tie: 1st place 1.5 points
            0 points for 3rd
     
    Doubles: 2 points for each bowler for 1st
                    1 points for each bowler for 2nd
                    0 points for each bowler for 3rd
     
    Ties: 1.5 points for each bowler for 1st
                0 points for 3rd

  59. LIM WEI CHON says:

    I got problem to sumproduct(sumif( for multiple criteria’s range.

    I got 2 criteria range how to sum them??

    SUMPRODUCT(SUMIFS($G$52:$G$7385,$B$52:$B$7385,P47,$O$52:$O$7385,”>=”&Q47,$O$52:$O$7385,”<="&R47,$L$52:$L$7385,$T$41:$T$46,$I$52:$I$7385,$T$27:$T$40))

Leave a Reply