Conditional Aggregation
Count Active Rows Not Equal to Multiple Terminal Statuses
Count non-blank records whose status does not match any item in an excluded list using SUMPRODUCT boolean inequalities or multi-criteria COUNTIFS.
Distinguish exact status inequality from partial substring exclusions
When workflow statuses share root words — such as "Review" vs "Closed - Review Complete" — wildcard exclusions ("<>*Closed*") may behave differently from exact inequality ("<>Closed"). Exact status exclusion checks whether a cell equals any terminal state in an exclusion list while keeping partial variants intact.
Multiplying elementwise inequalities --(B2:B7 <> "") * --(B2:B7 <> E2) * --(B2:B7 <> E3) * --(B2:B7 <> E4) inside SUMPRODUCT (or chaining "<>" & E2 inside COUNTIFS) counts active open items cleanly without helper columns.
| Ticket | Status | Terminal Exclusion Match? | Counted as Active? |
|---|---|---|---|
| TCK-201 | In Progress | None | Yes (1) |
| TCK-202 | Closed | Matches Closed | No |
| TCK-203 | Pending QA | None | Yes (2) |
| TCK-204 | Cancelled | Matches Cancelled | No |
| TCK-205 | Escalated | None | Yes (3) |
| TCK-206 | Archived | Matches Archived | No |
Active Ticket Count Excluding Terminal Status List
Counts non-blank statuses in B2:B7 that are not equal to B9, B10, or B11.
=COUNTIFS(B2:B7, "<>", B2:B7, "<>" & B9, B2:B7, "<>" & B10, B2:B7, "<>" & B11)Weighted Open Effort Hours Excluding Terminal Statuses
Sums estimated hours in C2:C7 for rows whose status is not in the excluded terminal list.
=SUMPRODUCT(--(B2:B7 <> ""), --(B2:B7 <> B9), --(B2:B7 <> B10), --(B2:B7 <> B11), C2:C7)Try the example
Use the original values or change them to test your understanding.