How to / Sums and averages

How to use SUMIF with OR (this or that) in Excel

Add one SUMIF for each value: =SUMIF(A2:A5, "North", B2:B5)+SUMIF(A2:A5, "South", B2:B5).

Sales for North or South

ABCD
1RegionSales
2North12002300
3South800
4West400
5North300

In D2: =SUMIF(A2:A5, "North", B2:B5)+SUMIF(A2:A5, "South", B2:B5) → 2300

1,200 + 300 for North, and 800 for South.

How it works

  1. SUMIF takes one condition. SUMIFS takes several, but a row must meet all of them, which is AND.
  2. For OR, work out each total on its own and add them.
  3. A region cannot be both North and South, so no row is counted twice.

Variations

  1. Shorter, with a list of values

    ABCD
    1RegionSales
    2North12002300
    3South800
    4West400
    5North300

    In D2: =SUM(SUMIF(A2:A5, {"North","South"}, B2:B5)) → 2300

    The list in braces makes SUMIF return one total per value, and SUM adds them.

  2. Count the rows instead

    ABCD
    1RegionSales
    2North12003
    3South800
    4West400
    5North300

    In D2: =COUNTIF(A2:A5, "North")+COUNTIF(A2:A5, "West") → 3

  3. OR across two different columns

    ABCD
    1RegionSales
    2North12002300
    3South800
    4West400
    5North300

    In D2: =SUMPRODUCT(((A2:A5="North")+(B2:B5>=800)>0)*B2:B5) → 2300

    North, or 800 and over. A row that meets both tests must count once, so the tests are added and compared with 0.

Watch out for

Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.

Functions used

Practise it

Related