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 equation 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:
- Combine functions for powerful logic
- Troubleshoot with confidence
- Build adaptive, error-resistant formulas
Learn With Us