SUMIFS with Multiple Criteria and OR Logic

Yes, you can use OR logic inside SUMIFS by adding multiple criteria ranges in an array constant or by summing separate SUMIFS functions. The standard SUMIFS function applies AND logic to all criteria, but with a few formula tricks you can sum cells that meet any one of several conditions.

Formula

For a simple demonstration, the calculator above sums a single amount if either of two numeric criteria is met. The formula is:

OR Sum = Amount × ( (Criteria1 > 0) + (Criteria2 > 0) )

In words: if either condition is true (value greater than 0), the expression inside the parentheses equals 1, so the amount is included. If neither is true, it equals 0. This is a simplified illustration; real SUMIFS with OR logic uses ranges and array constants.

How SUMIFS Works by Default

SUMIFS sums cells in a range that meet all specified criteria. The syntax is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Each criteria range is paired with a condition. If you provide three criteria, a cell is summed only if it satisfies all three. This is AND logic—every condition must be true. For example, to sum sales where region is “East” and product is “Widget”:

=SUMIFS(Sales, Region, "East", Product, "Widget")

This returns the total only for rows that are both East and Widget.

Adding OR Logic: The Array Constant Method

To sum when any of several criteria is met, you can use an array constant inside SUMIFS. For example, to sum sales where region is “East” OR “West”:

=SUM(SUMIFS(Sales, Region, {"East","West"}))

Here’s how it works:

• The array constant {"East","West"} makes SUMIFS return two separate sums—one for East, one for West.
• The outer SUM adds those two results together.
• If a row could match both criteria (e.g., if you were checking two different columns), this method would double-count. But when criteria are in the same column, each row matches only one, so it’s safe.

You can extend this to more values: {"East","West","North"}.

OR Logic Across Different Columns

If you need OR logic across different columns—for example, sum sales where region is “East” OR product is “Widget”—you can’t use a simple array constant because a row could match both conditions and be counted twice. Instead, use SUMPRODUCT:

=SUMPRODUCT(Sales * ((Region="East") + (Product="Widget")))

This works because (Region="East") returns an array of TRUE/FALSE, which Excel treats as 1/0. Adding the two arrays gives 1 if either condition is true, 2 if both are true. Multiplying by Sales then sums only the rows with at least one match. If you want to avoid double-counting rows that match both, use:

=SUMPRODUCT(Sales * (((Region="East") + (Product="Widget")) > 0))

This converts any positive count to 1.

Using SUMIFS with OR Logic and Other AND Criteria

Often you need OR logic for one set of criteria while keeping AND logic for others. For example, sum sales where (region is East OR West) AND product is Widget. You can combine the array constant method with additional SUMIFS criteria:

=SUM(SUMIFS(Sales, Region, {"East","West"}, Product, "Widget"))

This sums East Widgets and West Widgets separately, then adds them. The Product criterion is applied to both, so it acts as an AND with the OR set.

For OR across different columns with an AND condition, use SUMPRODUCT with multiplication:

=SUMPRODUCT(Sales * ((Region="East") + (Product="Widget")) * (Year=2024))

Common Pitfalls and How to Avoid Them

• Double-counting: When using array constants, ensure criteria are mutually exclusive (e.g., same column). If not, use SUMPRODUCT with the >0 trick.
• Text vs. numbers: Criteria in quotes are text; numbers without quotes are numeric. Ensure your data types match.
• Wildcards: SUMIFS supports * and ? for partial matches. For literal asterisks, use a tilde (~).
• Blank cells: A criterion of “” matches empty cells; “<>” matches non-empty. Use these carefully.
• Array entry: In older Excel versions, you may need to press Ctrl+Shift+Enter for array formulas. In Excel 365 and 2021, dynamic arrays handle this automatically.

Alternatives: SUMPRODUCT, FILTER, and Power Query

Besides SUMIFS, you have other options:

• SUMPRODUCT: As shown above, it’s flexible for complex OR logic and doesn’t require array entry in most versions.
• FILTER function: In Excel 365, you can filter the data first, then sum: =SUM(FILTER(Sales, (Region="East")+(Product="Widget"))). This is readable and avoids double-counting if you use the + operator (which returns 1 or 2) or the OR function (which returns TRUE/FALSE).
• Power Query: For large datasets, you can load data into Power Query, filter rows using OR conditions, and then sum. This is more advanced but efficient for repeated use.

Frequently Asked Questions

Can I use OR logic directly in SUMIFS without array constants?

No, SUMIFS itself only supports AND logic. You must use array constants, SUMPRODUCT, or the FILTER function to achieve OR logic.

Why does my SUMIFS with an array constant return a #VALUE! error?

This often happens when the array constant has different dimensions than the criteria range, or when you’re using an older Excel version that requires Ctrl+Shift+Enter. Ensure the array is horizontal (comma-separated) and confirm you’re entering it correctly.

How do I sum with OR logic for numeric criteria, like amount > 100 OR amount < 50?

Use SUMPRODUCT: =SUMPRODUCT(Sales * ((Amount>100)+(Amount<50))). This works because each condition returns 1/0, and the sum adds them.

What if I need to exclude certain values?

Use "<>" criteria, e.g., SUMIFS(Sales, Region, "<>East"). For multiple exclusions, you can use array constants with "<>" but be careful—this can get complex. SUMPRODUCT with NOT conditions is often simpler.

Does the order of criteria matter in SUMIFS?

No, the order doesn't affect the result. SUMIFS evaluates all criteria simultaneously, so rearranging them gives the same sum.

Can I use OR logic with SUMIFS across multiple sheets?

Yes, but you need to reference each sheet's ranges separately. You can use the array constant method with 3D references, but it's often easier to consolidate data or use SUMPRODUCT with INDIRECT for dynamic sheet references.

Related

• Best Wuxia Games Like Crouching Tiger, Hidden Dragon
• MW4 Skill-Based Matchmaking: Why Players Are Frustrated
• Joker Malware on Play Store: What It Is

in amount | Amount | $ | 100 | 0 | 1000000
in crit1 | Criteria 1 | - | 10 | 0 | 1000
in crit2 | Criteria 2 | - | 20 | 0 | 1000
out or_sum | OR Sum | $ | amount * ( (crit1 > 0) + (crit2 > 0) ) | 2