Math
Sum If Not Equal To ("<>") Single or Multiple Exclusions (SUMIFS)
Total ledger amounts while excluding cancelled, refunded, or intercompany categories using SUMIF(Cat, "<>" & Excl, Amt) and multi-exclusion SUMIFS(Amt, Cat, "<>" & Excl1, Cat, "<>" & Excl2).
Exclude one or several status codes without filtering rows out by hand!
To sum all revenue EXCEPT a single excluded category (such as "Cancelled" or a dynamic exclusion cell E2), concatenate the inequality operator "<>" with the cell reference: =SUMIF(A2:A6, "<>" & E2, B2:B6)! Never write "<>E2" inside quotes—putting E2 inside the quotes searches for the literal text string "E2" rather than the value stored in cell E2!
What if you need to exclude TWO or MORE categories at once (for example, NOT "Cancelled" AND NOT "Refunded")? Repeat the same category range inside SUMIFS with AND logic: =SUMIFS(B2:B6, A2:A6, "<>" & E2, A2:A6, "<>" & E3)—every row must pass BOTH exclusions to be summed!
| Exclusion Rule (Total Gross = $1,450) | Formula | Excluded Rows | Net Sum |
|---|---|---|---|
| Exclude E2 ("Cancelled" = $250) | =SUMIF(A2:A6, "<>" & E2, B2:B6) | Drops $250 | $1,200 |
| Exclude BOTH "Cancelled" & "Refunded" ($150) | =SUMIFS(B2:B6, A2:A6, "<>" & E2, A2:A6, "<>" & E3) | Drops $250 + $150 | $1,050 |
| Exclude Any Substring "*Test*" | =SUMIF(A2:A6, "<>*Test*", B2:B6) | Wildcard exclusion | Drops test orders |
Single Dynamic Category Exclusion via SUMIF(Range, "<>" & Cell, SumRange)
Sums B2:B6 across all rows where A2:A6 does not equal E2 (including blank category cells).
=SUMIF(A2:A6, "<>" & E2, B2:B6)Multi-Value Exclusion ("Not X AND Not Y") via Repeated Range in SUMIFS
Excludes both E2 ("Cancelled") and E3 ("Refunded") simultaneously.
=SUMIFS(B2:B6, A2:A6, "<>" & E2, A2:A6, "<>" & E3)Try the example
Use the original values or change them to test your understanding.