Two regions in one formula (SUMIF with an OR list)
The problem
Problem statement
The order log in A2:C11 mixes four regions, and the review deck needs North and East combined — one number, not two. Put the combined total of those two regions in the North + East total cell E2.
SUMIF returns one number per criterion, so handing it two regions is only half the job — the outer layer has to add the pieces back together.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | E | |
|---|---|---|---|---|
| 1 | Region | Order | Sales | North + East total |
| 2 | North | ORD-2001 | 1250 | |
| 3 | South | ORD-2002 | 800 | |
| 4 | North | ORD-2003 | 960 | |
| 5 | East | ORD-2004 | 600 | |
| 6 | West | ORD-2005 | 700 | |
| 7 | North | ORD-2006 | 330 | |
| 8 | East | ORD-2007 | 480 | |
| 9 | South | ORD-2008 | 1200 | |
| 10 | West | ORD-2009 | 250 | |
| 11 | East | ORD-2010 | 90 |
Notes
A criterion can be an array constant: SUMIF(A2:A11, {"North","East"}, C2:C11) returns TWO totals, one per region, and wraps them in a value array. SUM() outside collapses that array into the single number the report needs. Adding two separate SUMIF calls builds the same answer the long way — fine for two regions, painful for six. The same array constant also works inside SUMIFS, which is the habit to keep once more criteria enter the picture.
How to approach
Your answer goes in E2. On submit we re-run your formula against 3 different sets of input, so a hardcoded number will fail — the formula has to follow the cells.
- Target cell is E2
- Regions are in A2:A11 and amounts in C2:C11
- The criteria argument can be an array constant like {"North","East"}, which makes SUMIF return one total per region instead of a single number
- Shape: =SUM(SUMIF(?, {?,?}, ?)) or =SUMIF(?,?,?) + SUMIF(?,?,?)
Official solutions
Show 2 solutions — try it yourself first
Approach 1: array-constant criteria wrapped in SUM
=SUM(SUMIF(A2:A11, {"North","East"}, C2:C11))
The array constant gives SUMIF two criteria, so it evaluates the sum twice and hands back a two-item array. Dropping the outer SUM leaves you with just the first region's total — the most common way this formula goes wrong. Add a third region to the constant and SUM scales without another term.
Approach 2: two SUMIF results added together
=SUMIF(A2:A11,"North",C2:C11)+SUMIF(A2:A11,"East",C2:C11)
Same total, spelled out. It is easier to read when each region already has its own labelled column, but it grows linearly: six regions mean six SUMIF calls and six chances for a typo.
Function guide
FAQ
How do I sum two or more specific values from the same column?
=SUM(SUMIF(A2:A11, {"North","East"}, C2:C11)) — the array constant holds the values you want, SUMIF returns one total per value, and SUM adds them up. It is the single-column equivalent of 'in this list'.
Why does my OR formula return only the first region?
Because SUMIF with an array constant returns an array, not a scalar. Without SUM outside, Excel shows only the first item of that array — the formula looks like it works while quietly dropping the other regions.
Does the array constant work with SUMIFS too?
Yes. =SUM(SUMIFS(C2:C11, A2:A11, {"North","East"})) returns the same total and keeps working when you add a second condition, which SUMIF cannot do.