How to use SUMIF in Excel with multiple OR conditions

Do you know how to sum numbers in a certain column when a value in another column meets any of the specified conditions? In this article, you will learn 3 different ways to do SUMIF using multiple criteria and OR logic.

Microsoft Excel has a special function to sum cells with multiple conditions - the SUMIFS function. This function is designed to work with AND logic - a cell is added only when all of the specified criteria are TRUE for that cell. In some situations, however, you may need to sum with multiple OR criteria, i.e. to add a cell when any of the conditions is TRUE. And this is when the SUMIF function comes in handy.

SUMIF + SUMIF to sum cells equal to this or that

When you are looking to sum numbers in one column when another column is equal to either A or B, the most obvious solution is to handle each condition individually, and then add the results together:

SUMIF(range, criteria1, sum_range) + SUMIF(range, criteria2, sum_range)

In the table below, suppose you want to add up sales for two different products, say Apples and Lemons. For this, you can supply the items of interest directly in the criteria arguments of 2 different SUMIF functions:

=SUMIF(A2:A10, "apples", B2:B10) + SUMIF(A2:A10, "lemons", B2:B10)

Or you can enter the criteria in separate cells, and refer to those cells:

=SUMIF(A2:A10, E1, B2:B10) + SUMIF(A2:A10, E2, B2:B10)

Where A2:A10 is the list of items (range), B2:B10 are the numbers to sum (sum_rage), E1 and E2 are the target items (criteria):
Excel SUMIF formula with multiple criteria

How this formula works:

The first SUMIF function adds up the Apples sales, the second SUMIF sums the Lemons sales. The addition operation adds the sub-totals together and outputs the total.

SUMIF with array constant - compact formula with multiple criteria

The SUMIF + SUMIF approach works fine for 2 conditions. If you need to sum with 3 or more criteria, the formula will become too big and difficult to read. To achieve the same result with a more compact formula, supply your criteria in an array constant:

SUM(SUMIF(range, {crireria1, crireria2, crireria3, …}, sum_range))

Please remember that this formula works based on OR logic - a cell is summed when any single condition is met.

In our case, to sum sales for 3 different items, the formula is:

=SUM(SUMIF(A2:A10, {"Apples","Lemons","Oranges"}, B2:B10))
SUMIF formula with array constant to sum cells with multiple OR criteria

In the above screenshot, the conditions are hardcoded in an array, meaning you will have to update the formula with every change in the criteria. To avoid this, you can input the criteria in predefined cells and supply to a formula as a range reference (E1:E3 in this example).

=SUM(SUMIF(A2:A10, E1:E3, B2:B10))

In Excel 365 that supports dynamic arrays, it works as a regular formula completed with the Enter key. In pre-dynamic versions of Excel 2019, Excel 2016, Excel 2013 and earlier, it should be entered as an array formula with the Ctrl + Shift + Enter shortcut:
SUM and SUMIF formula with multiple criteria in different cells

How this formula works:

An array constant plugged into SUMIF's criteria forces it to return multiple results in the form of an array. In our case, it's 3 different amounts: for Apples, Lemons and Oranges:

{425;425;565}

To get the total, we use the SUM function and wrap it around the SUMIF formula.

SUMPRODUCT and SUMIF to sum cells with multiple OR conditions

Don't like arrays and are looking for a normal formula that would allow you to sum with multiple criteria in different cells? No problem. Instead of SUM, use the SUMPRODUCT function that handles arrays natively:

SUMPRODUCT(SUMIF(range, crireria_range, sum_range))

Assuming the conditions are in cells E1, E2 and E3, the formula takes this shape:

=SUMPRODUCT(SUMIF(A2:A10, E1:E3, B2:B10))
SUMPRODUCT and SUMIF to sum cells with multiple OR conditions

How this formula works:

Like in the previous example, the SUMIF function returns an array of numbers, representing the sums for each individual condition. SUMPRODUCT adds these numbers together and outputs a final total. Unlike the SUM function, SUMPRODUCT is designed to process arrays, so it works as a regular formula without you having to press Ctrl + Shift + Enter.

SUMIF using multiple criteria with wildcards

Since the Excel SUMIF function supports wildcards, you can include them in multiple criteria if needed.

For example, to sum sales for all sorts of Apples and Bananas, the formula is:

=SUM(SUMIF(A2:A10, {"*Apples","*Bananas"}, B2:B10))

If your conditions are supposed to be input in individual cells, you can type wildcards directly in those cells and provide a range reference as criteria for the SUMPRODUCT SUMIF formula:
SUMIF using multiple criteria with wildcards

In this example, we put a wildcard character (*) before the item names to match any preceding sequence of characters such as Green apples and Goldfinger bananas. To get a total for items that contain specific text anywhere in a cell, place an asterisk on both sides, e.g. "*apple*".

That's how to use SUMIF in Excel with multiple conditions. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

SUMIF multiple criteria (.xlsx file)

27 comments

  1. Company 1 Company 1 Company 2 Company 2
    Jan Feb Jan Feb

    Green 1 4 9 12
    Blue 2 5 7 9
    Orange 3 8 4 4

    How do I sum up (not vlook up) Company 1 in Feb for Green please? I tried sumifs, index match but doesnt work think it doesnt like two criteria on the horizontal perhaps?

  2. Hi,
    Thanks for all the info/help... just wondering if you can tell me which function to use in this case: I want to add amounts in column A if either column B, C, D, E contain "x". "X" is a destination from a table in column AA (AA1:AA10). What would be the most efficient way to do this?
    Thanks!

  3. I would like to sum under both an and AND an or condition. Is this possible?
    For example If the text in column A is either x OR y, AND the text in column B is z, then Sum column C.

  4. Hi Alexander

    I have two formulas that I'd like your assistance with please:

    1. What formula should I use to allow the sum of multiple text options (potential, yes and no) in column AE. I tried the below but it doesn't work.

    =SUMIFS('WCC Sites'!Z3:Z84, 'Sites'!AE3:AE84, {"Potential", "Yes", "No"}, 'Sites'!H3:H84, "=23/24")

    2. How can I shorten the below? There's two worksheets (Sites & Stone), each with three options (potential, yes and no) that I would like the sum of the price for (Q2:Q86).

    =SUMIF('WCC Sites'!AE2:AE85, "Potential", 'Sites'!Z2:Z85) + SUMIF('Sites'!AE2:AE85, "Yes", 'Sites'!Z2:Z85) + SUMIF('Sites'!AE2:AE85, "No", 'Sites'!Z2:Z85) + SUMIF('Stone'!V2:V86, "Potential", 'Stone'!Q2:Q86) + SUMIF('Stone'!V2:V86, "No", 'Stone'!Q2:Q86) + SUMIF('Stone'!V2:V86, "Yes", 'Stone'!Q2:Q86)

    Many thanks

    1. Hi! I can't check your formula, but it should return 3 values. Use the SUM function.

      =SUM(SUMIFS('WCC Sites'!Z3:Z84, 'Sites'!AE3:AE84, {"Potential", "Yes", "No"}, 'Sites'!H3:H84, "=23/24"))

      You can use this in the second formula as well. For example:

      SUM(SUMIF('Sites'!AE2:AE85, {"Yes","No"}, 'Sites'!Z2:Z85))

  5. does anybody now how to solve the following problem:
    How to sum-up values considering two column with each multiple critera, e.g. sales revenue per (1) market per (2) segment,
    for (1) want to include US and France, but not UK
    for (2) want to include apples & peaches but no bananas
    Thanks for help

  6. =(SUMIF('sheet1'!I2:I,"PT",'sheet1'!K2:K)*('sheet1'!L2:L))

    I have to multiply numbers from column 1 with column 2 and them display the total sum

  7. Good Day

    Hope you can assist me,

    When I use the following formula "=SUMIFS($Y:$Y,$B:$B, ">="&$X$1,$B:$B, "<="&$Y$1,$A:$A,$E2)" [$Y:$Y,$B:$B, - different workbook data] to calculate a value between two dates (X&Y) where the dates are in a fixed cell it only works when the data sheet that I'm getting the data from is open (Different Workbook). I had the same issue with this previously and was advised too use the "SUM(IF(" function instead of the "SUMIFS"

    When I use the same Formula detail as with the SUMIFS function with the SUM(IF( it duplicates the values or returns an error

    Is there a way i can use this SUMIFS or SUM(IF( correctly so that it does not return an error when the database workbook is closed

    Hope this makes sense

    Regards

    1. Hi!
      The Excel functions you are using can only get data from open workbooks. Your problem can be solved with VBA.

  8. excel formula to:

    input data from cell B3 into cell D3, IF cell C3 does not equal criteria_1 OR does not equal criteria_2

    2. Figured it out:

      =SUMIFS(D3,B3,"criteris_1",D3,"criteria_2")

  9. =SUM(SUMIFS(J2:J2646,F2:F2646,{"RUH-G1","RUH-G2"},G2:G2646,{"RUH-G1","RUH-G2"},H2:H2646,{"RTO"}))
    Please help me not received right result.

  10. Thanks for your helpful articles. Very clear and well presented. I am trying to use the SUMIF formula to extract a total value from 1 column using a qualifying value detailed in another column between defined limits eg 1.60 to 2.0. I tried using "2.0" which gives an error message. I then tried SUMIFS but this appears to need values in 2 different columns to work. Is there a further article that would help here please? You can see the spreadsheet on this link if it helps. https://docs.google.com/spreadsheets/d/1BgZqQ-R5PGkoRtVwStJN-pl9TH57LIBDqpZPvA6WIoU/edit?usp=sharing The cell in question is D6 and E5 will need a similar solution ( I have used a simple work around for the time being but this will only work if I do not want to add further analysis). Thanks and regards Peter

  11. i am trying to set formulae for calculating figures in various sheets for dated 1st to 30th of month in which all the figures are in same column i,e for example A5 of sheet 1,sheet 2.

    please suggest me the formulae

  12. Hi,

    I have the following formula

    =IF(ISNUMBER(SEARCH("fred",B12))*ISNUMBER(SEARCH("saturday",L12)),E12*$M$2*$R$5,"")

    This give the desired output for fred and Saturday but I need to add the same again but for fred and Sunday, which would also change the refernce M2 to R2. So the equiv' formula for fred and Sunday is

    =IF(ISNUMBER(SEARCH("fred",B12))*ISNUMBER(SEARCH("sunday",L12)),E12*$R$2*$R$5,"")

    How do I combine the two?

    Thanks

    Ian

    1. Hello!
      Add a condition to test the value of cell L12

      =IF(ISNUMBER(SEARCH(“saturday”,L12)), IF(ISNUMBER(SEARCH(“fred”,B12))*ISNUMBER(SEARCH(“saturday”,L12)),E12*$M$2*$R$5,””), IF(ISNUMBER(SEARCH(“fred”,B12))*ISNUMBER(SEARCH(“sunday”,L12)),E12*$R$2*$R$5,””))

      I hope it’ll be helpful.

  13. 12D, 6N, R, N, L, D, SL, R, N, D, 9N. CAN YOU PLEASE HELP ME TO SUM THIS ALPHANUMERICS, BUT BY OMITTING THE LETTERS I SHOULD GET THE ANSWER AS = 27.

    THANKS A LOT

    1. Hello!
      To extract and sum all single and double digit numbers from text, use the formula

      =SUM(IF(ISNUMBER(--MID(A1,ROW($1:$93),2)),--MID(A1,ROW($1:$93),2),""))

      Hope this is what you need.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)