Excel SUM with OR Criteria

Ah, SUM - the first Excel function most of us learn. It’s great for adding up columns and rows, but what if I told you that SUM can do the job of COUNTIFS, SUMIFS, even AVERAGEIFS - and it can even overcome their limitations?

Let’s dive into some surprising and powerful tricks you can do with SUM alone..

Watch SUM with OR Criteria Video

 

Get the Excel Example File

Conditional Counting with SUM

Let’s start with a common task: counting rows that meet multiple conditions. I’ll be using the data below throughout the examples. It’s formatted in an Excel Table called ‘Sales’ and I’ll be using the Table’s structured references in the formulas to make them quick and easy to write.

Let’s say we want to count how many days we sold more than $1000 worth of Mice.

✅ Using COUNTIFS:

=COUNTIFS(

Sales[Sales Value], ">" & 1000,

Sales[Item], "Mouse"

)

This counts records where the Sales Value > 1000 AND Item = "Mouse".

✅ Using SUM instead:

=SUM(

(Sales[Sales Value] > 1000) *

(Sales[Item] = "Mouse")

)

Here’s what’s happening:

1. The logical tests return arrays of TRUE/FALSE:

=SUM(

(FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE…) *

(FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE…) )

2. Multiplying them coerces these values into their numeric equivalents of 1 and 0.

=SUM(

(0;0;0;1;0;1;0…) *

(0;0;0;1;0;0;0…) )

=SUM(  0;0;0;1;0;0;0… )

3. SUM then adds up the results. Only rows where both conditions are true are counted.

📌 AND criteria logic is built-in with multiplication.

OR Criteria: Same Column

Want to count rows where the item is either Mouse OR Keyboard?

❌ COUNTIFS won’t work:

=COUNTIFS(Sales[Item], "Mouse", Sales[Item], "Keyboard")

This returns 0 because COUNTIFS doesn’t support OR logic across the same column.

✅ Use SUM:

=SUM(

(Sales[Item] = "Mouse") +

(Sales[Item] = "Keyboard")

)

Here, we add the conditions. If either is true, the result is 1.

OR Criteria: Different Columns (Avoid Double Counting!)

Let’s say we want to count:

• Rows where the item is Mouse, OR
• Sales value is greater than $1000.

⚠️ Watch out for double counting:

=SUM(

(Sales[Item] = "Mouse") +

(Sales[Sales Value] > 1000)

)

Rows matching both conditions will be counted twice. For example, in the image below we can see the fourth row matches both criteria and as a result, adding the two conditions together returns 2, so when SUM aggregates the values, it will count this row twice:

✅ Use SIGN to fix it:

The SIGN function converts any number > 0 to 1, eliminating double counting.

=SUM(

SIGN(

(Sales[Item] = "Mouse") +

(Sales[Sales Value] > 1000)

)

)

Conditional Sums

Let’s extend this to summing values. For example, to sum Sales Value for:

• Region = "East", AND
• Category = "Toys" OR "Clothing"

✅ Use this formula:

=SUM(

Sales[Sales Value] *

(Sales[Region] = "East") *

(

(Sales[Category] = "Toys") +

(Sales[Category] = "Clothing")

)

)

Breakdown:

Multiplication acts as an AND condition. Addition acts as an OR condition.

The OR conditions don’t require the SIGN function here because they operate over the same column, so there’s no risk double counting, but they do need to be enclosed in a second set of parentheses.

Conditional Averages

Let’s say we want to find the average sales value for T-Shirts. It’s easy with AVERAGEIFS:

✅ AVERAGEIFS:

=AVERAGEIFS(Sales[Sales Value], Sales[Item], "T-Shirt")

✅ It’s Complicated with SUM:

=SUM(Sales[Sales Value] * (Sales[Item] = "T-Shirt")) /

 SUM(--(Sales[Item] = "T-Shirt"))

The logical test in the denominator doesn’t have a math operation performed on it. To get around this, we can use two minus signs (--) to coerce TRUE/FALSE to 1/0.

✅ Average for OR Conditions:

Again, AVERAGEIFS cannot handle OR conditions, but SUM can:

=SUM(Sales[Sales Value] * ((Sales[Item] = "T-Shirt") + (Sales[Item] = "Jacket"))) /

SUM((Sales[Item] = "T-Shirt") + (Sales[Item] = "Jacket"))

Note the double parentheses around the OR conditions.

Great for when you need to average values across multiple items.

Bonus: Searching Inside Text

Another use of these techniques is to search inside text for matching words. For example, here I have some product review data.

Let’s count how many product reviews mention the word “fast”.

✅ Use SEARCH and ISNUMBER:

=SUM(--ISNUMBER(SEARCH("fast", Reviews[Review])) )

It returns 8.

✅ For OR logic (e.g. “fast” OR “affordable”):

=SUM(SIGN(

ISNUMBER(SEARCH("fast", Reviews[Review])) +

ISNUMBER(SEARCH("affordable", Reviews[Review]))

)

)

It returns 13 and we can see the matching rows in the filtered table below:

You can now build advanced keyword detection formulas using just SUM.

Wrap-Up: Why Use SUM for Complex Logic?

Using SUM with Boolean logic and coercion techniques gives you:

• More flexibility than COUNTIFS / SUMIFS / AVERAGEIFS
• Support for OR conditions across columns
• Use SIGN to avoid double counting OR conditions
• Advanced search capability within text

Want to Go Further?

If this clicked for you and you're thinking, “I want more like this…”, check out our Advanced Excel Formulas Course. You’ll learn to:

• Build adaptive, error-resistant formulas
• Combine functions for powerful logic
• Troubleshoot with confidence