Spreadsheet Practice

How to exclude several values from a count with COUNTIFS in Excel

Formula (Excel and Google Sheets)

=COUNTIFS(B2:B11,"<>"&F1,B2:B11,"<>"&F2)

Give COUNTIFS one "not equal" condition per value on the same range: =COUNTIFS(B2:B11,"<>"&F1,B2:B11,"<>"&F2).

Result on the example
6 in F3
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in F3. Change any input and the result updates, or edit the formula to experiment.

How it works

COUNTIFS joins its conditions with AND, so a ticket counts only if its status is not "Closed" and not "Cancelled". Repeating the same range with a second condition is allowed.

Ten tickets minus two closed and two cancelled leaves six.

=COUNTIFS(B2:B11,"<>"&F1,B2:B11,"<>"&F2)

Result6

  1. B2:B1110 rows × 1 col: "Open", "Closed", "In progress", "Cancelled", …

    A range: 10 cells in one column.

  2. F1"Closed"

    The value in F1.

  3. "<>"&F1"<>Closed"

    The & operator joins as text.

  4. F2"Cancelled"

    The value in F2.

  5. "<>"&F2"<>Cancelled"

    The & operator joins as text.

  6. COUNTIFS(B2:B11,"<>"&F1,B2:B11,"<>"&F2)6

    COUNTIFS: Counts rows that meet every condition.

    • criteria_range1B2:B1110 rows × 1 col: "Open", "Closed", "In progress", "Cancelled", …
    • criteria1"<>"&F1"<>Closed"
    • criteria_range2B2:B1110 rows × 1 col: "Open", "Closed", "In progress", "Cancelled", …
    • criteria2"<>"&F2"<>Cancelled"

In Google Sheets

The same formula works in Google Sheets: =COUNTIFS(B2:B11,"<>"&F1,B2:B11,"<>"&F2). We ran it in Google Sheets mode on the same data and it returns 6.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=ROWS(B2:B11)-SUM(COUNTIF(B2:B11,F1:F2))6All rows minus the ones to exclude. COUNTIF with a range of criteria returns one count per value, so the list can be as long as you like.
=SUMPRODUCT(--ISNA(MATCH(B2:B11,F1:F2,0)))6Counts statuses that MATCH can't find in the exclude list. Best when the list is long and kept in cells.

Common mistakes

  • Using COUNTIF with an array, like =COUNTIF(B2:B11,{"<>Closed","<>Cancelled"}), returns two counts that each include the other status. Summing them double counts.
  • Blank statuses are counted as "not Closed". Add ,B2:B11,"<>" to skip them.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Exclude several values from a count with COUNTIFS

In F3, count the tickets whose status is neither F1 nor F2.

Questions

How do I exclude values and also require one?

Mix the conditions: =COUNTIFS(B2:B11,"<>Closed",B2:B11,"<>Cancelled",C2:C11,"High").

Can the excluded values contain wildcards?

Yes: "<>*test*" excludes every status that contains "test".

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.