How to COUNTIF multiple values in Excel

Add one COUNTIF per value (any version)

The sheet is a support queue. You want to know how many tickets still need action, which means the status is Open or Pending. COUNTIF takes one value, so the simplest answer is one COUNTIF per value, added together. Open matches 3 tickets and Pending matches 2.

COUNTIF is not case-sensitive. T-1048 was typed as "open" in lower case, and it is still counted with the Open tickets. If you need a case-sensitive count, COUNTIF cannot do it; use =SUMPRODUCT(--EXACT(B2:B11,"Open")) instead.

This is fine for two or three values. Beyond that the formula gets long and it is easy to forget to update one copy when you change a range.

One formula: a list of values in braces

Put the values in curly braces, separated by commas. This is an array constant: COUNTIF runs once per value and returns three counts, 3, 2 and 1. SUM adds them into one number. It works as a normal formula in every version of Excel, no Ctrl+Shift+Enter needed.

Do not leave out SUM. In Excel 365 and 2021, =COUNTIF(B2:B11,{"Open","Pending"}) spills two separate counts, 3 and 2, into neighbouring cells. In Excel 2019 and earlier it shows only the first count, 3, which looks like a plausible answer and is wrong.

You type the braces yourself; only the outer braces Excel adds to array formulas are automatic. In some regional settings Excel uses a different separator inside array constants, so if Excel rejects the commas, check the list separator your version uses.

Why COUNTIFS with two values in one column returns 0

The first thing most people try is COUNTIFS with the status column twice. COUNTIFS joins its conditions with AND: a row counts only if the status is "Open" and also "Pending". No cell can be both, so the answer is always 0. COUNTIFS is the right tool for conditions on different columns, not for alternatives in the same column.

Typing the values into one string does not work either. =COUNTIF(B2:B11,"Open,Pending") looks for a cell that literally contains the text Open,Pending, and returns 0.

Keep the values in cells instead

Hard-coded values hide inside the formula. A short list on the sheet (G2:G4 here, headed "Needs action") is easier to read and change: add "Waiting on customer" to the list, extend the range, and the count follows. Point COUNTIF at the list instead of the braces.

Use SUMPRODUCT rather than SUM around it. In Excel 365 and 2021 SUM works too, but in Excel 2019 and earlier SUM with a range of criteria needs Ctrl+Shift+Enter, and without it you get a wrong count. SUMPRODUCT works the same everywhere.

Keep each value in the list once. If Open appears twice in G2:G4, every Open ticket is counted twice.

The count is lower than what you can see: stray spaces

Look at T-1050. It says Pending, but no formula above counts it. The cell holds "Pending " with a space at the end (=LEN(B11) returns 8, not 7), typical of data pasted from a form or another system. COUNTIF compares the whole text, so the space makes it a different value.

The lasting fix is to clean the column: select it and use Find and Replace to remove trailing spaces, or a TRIM helper column pasted back as values. To count correctly without touching the data, TRIM the statuses inside the formula and look each one up in the list with MATCH. ISNUMBER turns "found" into TRUE and -- turns TRUE into 1.

This version has a second advantage: each ticket is counted once, even if a value appears twice in the list. Without TRIM, =SUMPRODUCT(--ISNUMBER(MATCH(B2:B11,G2:G4,0))) gives 6. In Excel 365, =COUNT(XMATCH(B2:B11,G2:G4)) is a shorter way to write it and also gives 6, because COUNT skips the #N/A from rows that are not in the list.

Partial matches: the wildcard double count

To count tickets about refunds or returns, wildcards look like the answer: =SUM(COUNTIF(E2:E11,{"*refund*","*return*"})) returns 6. But T-1044, "Refund for a return", contains both words, so it is counted once for each. Only 5 tickets are actually about refunds or returns.

Test each row once instead. SEARCH returns a position when the word is found and an error when it is not, so ISNUMBER(SEARCH()) is TRUE or FALSE per row. Add the two tests, and >0 turns "matched one or both" into a single TRUE. SEARCH is not case-sensitive, like COUNTIF; use FIND if case matters.

Several values plus another condition

The braces work inside COUNTIFS too. Here the status must be Open or Pending, and the channel must be Email. COUNTIFS returns one count per status and SUM adds them. Only one condition can carry a list this way, unless you deliberately cross two lists.

Crossing two lists counts every combination: =SUM(COUNTIFS(B2:B11,{"Open","Pending"},D2:D11,{"High";"Medium"})) returns 4 (Open-High, Open-Medium, Pending-High and Pending-Medium). The first list uses commas (a row), the second semicolons (a column). If both used commas, Excel would pair them up position by position instead (Open with High, Pending with Medium) and you would get a different, usually wrong, number.

Alternatives in two different columns are a different problem: Status is Open or Priority is High. =COUNTIF(B2:B11,"Open")+COUNTIF(D2:D11,"High") returns 6, but T-1041 is both and is counted twice. Use =SUMPRODUCT(--(((B2:B11="Open")+(D2:D11="High"))>0)), which returns 5.

Which method to pick

Two or three fixed values: add COUNTIFs, or use SUM(COUNTIF()) with braces. A list that changes, or that other people edit: keep it in cells and use SUMPRODUCT(COUNTIF()). Data from an export or a form: count with TRIM and MATCH, or clean the column first. Partial matches with more than one word: test each row with ISNUMBER(SEARCH()) so no row is counted twice. To count everything except a few values, see the COUNTIF not equal guide.