COUNTIF With Multiple Values (OR Logic)

COUNTIF takes only one condition, so to count cells that match multiple values, such as Open or Pending, you combine COUNTIFs. The simplest way is to add them together; a shorter way puts the values in an array inside one COUNTIF. This guide shows both, plus how to use a list of values in cells and how to avoid counting the same cell twice.

The sample data#

Every example uses this list of support tickets in A1:C9:

There are 3 Open, 2 Pending, 2 Closed and 1 Escalated ticket. If you're new to the function, start with the COUNTIF guide or the short COUNTIF function reference.

How to COUNTIF multiple values by adding COUNTIFs#

Write one COUNTIF for each value and add the results with +. To count tickets that are Open or Pending:

=COUNTIF(C2:C9,"Open")+COUNTIF(C2:C9,"Pending")

The result is 5 (3 + 2). This works in every version of Excel and is easy to read. Each cell holds only one status, so no ticket is counted twice.

The downside is length. With four or five values, the formula gets long and it is easy to miss a range when you edit it.

A shorter way: COUNTIF with an array of values#

Put the values inside curly brackets, separated by commas, and wrap COUNTIF in SUM:

=SUM(COUNTIF(C2:C9,{"Open","Pending"}))

The result is again 5. The curly brackets make an array constant, a fixed list of values typed into the formula. COUNTIF runs once for each value and returns two counts, {3,2}. SUM then adds them up.

Without SUM, older versions of Excel show only the first count, 3. In Microsoft 365 the two counts spill into two cells instead. So always keep the SUM. You type the curly brackets yourself; this formula needs no special keys.

How to count values listed in cells#

When the values to count sit in cells, point COUNTIF at that range and wrap it in SUMPRODUCT. Say F2:F3 hold Open and Pending:

=SUMPRODUCT(COUNTIF(C2:C9,F2:F3))

The result is 5. Now you can change the list without touching the formula, and adding a third value only means extending the range to F2:F4.

=SUM(COUNTIF(C2:C9,F2:F3)) also works in Microsoft 365 and Excel 2021. In Excel 2019 and older it must be confirmed with Ctrl+Shift+Enter as an array formula, which is easy to forget. SUMPRODUCT works everywhere with a normal Enter.

Why COUNTIFS returns 0 for multiple values#

COUNTIFS uses AND logic: a row counts only if every condition is true. So this returns 0:

=COUNTIFS(C2:C9,"Open",C2:C9,"Pending")

No cell can be Open and Pending at the same time. This is one of the most common reasons for a surprise zero, along with the others in why COUNTIF is not working.

AND logic is useful for exclusions, though. To count tickets that are neither Closed nor Escalated:

=COUNTIFS(C2:C9,"<>Closed",C2:C9,"<>Escalated")

The result is 5: every ticket except T-02, T-05 and T-06. The same pattern counts numbers in a band, such as ">=10" and "<=20"; see COUNTIF greater than and less than.

How to count multiple values with wildcards#

Wildcards work inside the array too. To count categories that start with B or S:

=SUM(COUNTIF(B2:B9,{"B*","S*"}))

The result is 5 (3 Billing + 2 Shipping). The * matches any characters after the letter. The lesson on wildcards and cell-based criteria covers * and ? in detail.

Be careful when the patterns can overlap. =SUM(COUNTIF(B2:B9,{"*bill*","*ing*"})) returns 8, not 5. Every Billing cell matches both patterns, so each one is counted twice.

To count each row once, test every cell with SEARCH and count the rows where at least one test is true:

=SUMPRODUCT(--((ISNUMBER(SEARCH("bill",B2:B9))+ISNUMBER(SEARCH("ing",B2:B9)))>0))

This returns 5. SEARCH finds the text anywhere in the cell, ISNUMBER turns a match into TRUE, and >0 keeps any row with at least one match. More on partial matches in COUNTIF contains text.

How to count OR conditions across two columns#

When the two values are in different columns, adding COUNTIFs can count a row twice. To count tickets that are Billing or Open, subtract the rows that are both:

=COUNTIF(B2:B9,"Billing")+COUNTIF(C2:C9,"Open")-COUNTIFS(B2:B9,"Billing",C2:C9,"Open")

That is 3 + 3 - 2 = 4 (T-01, T-03, T-04 and T-08). Without the subtraction, T-01 and T-08 would each be counted twice. If you need how many different values there are, rather than how many matches, see how to count unique values.

Which method should you use?#

Pick the method by where the values live and how many there are. All four give the same count on clean data.

For a job test, the added COUNTIFs are the safest answer, because anyone checking your work can read them at a glance. In your own reports, a list of values in cells is easier to maintain: when a manager asks to add "Escalated" to the count, you type one word in a cell instead of editing the formula. Whatever you choose, check the result once by hand on a small range before you trust it on thousands of rows.

Practise it#

OR counts come up often in reports and job tests. Practise COUNTIF in a live spreadsheet: you type each formula and the lesson checks it instantly. Then practise wildcards and cell-based criteria to count partial matches and values from a list.