[The Problem]
Have you ever been stuck in this situation while working on a report?
“I need the total sales, but I want to exclude all ‘Refunded’ items…”
Usually, people calculate the grand total and then manually subtract the specific items using a calculator. If you have hundreds of rows, this is not only time-consuming but also prone to errors. 😭
Today, I’ll show you a magic trick using the SUMIF function and the <> symbol to exclude specific data and sum the rest in just 1 second!
[The Solution: Step-by-Step]
The secret is the <> symbol. In Excel language, this means “Not equal to.” It tells Excel: “Sum everything EXCEPT this!”
- Click the cell where you want the result.
- Enter the formula below:
- Formula: =SUMIF(Range, “<>ItemToExclude”, Sum_Range)
- ✨ Don’t forget: Place <> before the word you want to exclude and wrap the whole thing in double quotes (“”).
For example, to exclude “Banana”, you simply write “<>Banana” in the criteria part.
[Example Data Table]
Let’s look at this ‘Fruit Sales’ table. We want to calculate the total sales excluding Bananas.
| Row | A (Fruit) | B (Amount) |
| 1 | Apple | 2,000 |
| 2 | Banana | 5,000 |
| 3 | Grape | 3,000 |
| 4 | Banana | 5,000 |
| 5 | Watermelon | 10,000 |
✅ Type this:
=SUMIF(A1:A5, "<>Banana", B1:B5)🧮 Result: 15,000
(It ignored the Bananas and summed Apple + Grape + Watermelon!)
🍯 [Bonus Tip] Want to make it dynamic?
Is it annoying to rewrite the formula like “<>Apple”or “<>Banana”every time?
You can type the item you want to exclude in a specific cell (e.g., K5) and link your formula to it!
The secret here is the &(Ampersand) symbol.
- Wrong: SUMIF(…, “<>K5”, …) ❌ (Excel will look for the text “K5”)
- Right: SUMIF(…, “<>”&K5, …) ⭕
Keep the operator <>inside the quotes, put the cell reference K5 outside, and join them together with &. Now, simply change the text in cell K5, and the total updates automatically! 😎
[Summary]
Stop the manual subtraction! Just remember the “<>Value” rule (or “<>”&Cell) inside SUMIF. This simple trick allows you to smartly calculate sums while ignoring specific data. Work smarter, not harder! 🚀