Excel COUNTIF for Multiple Values: How to Use

Formulas & functions · Updated November 22, 2026

To count cells matching any of several values in Excel, use SUM(COUNTIF(range, {"value1","value2",...})) as an array formula (or with SUMPRODUCT for older versions). For example, =SUM(COUNTIF(A2:A10,{"Red","Blue"})) counts all cells in A2:A10 that contain either “Red” or “Blue”.

Why COUNTIF Alone Isn’t Enough

The standard COUNTIF function accepts only one condition. If you try =COUNTIF(A2:A10,"Red","Blue"), Excel returns an error because the syntax is COUNTIF(range, criteria)—only two arguments. To handle multiple values, you need to combine several COUNTIF results or use a more advanced function.

This need arises frequently when you have categories, statuses, or product names and want a total across a subset. For instance, you might want to count all orders that are “Shipped” or “Delivered” without counting “Cancelled”.

Method 1: SUM with COUNTIF Array Constant

The most direct approach uses an array constant to hold the values you’re matching. Here’s the layout and formula:

Layout: Assume your data is in cells A2:A20, and you want to count how many cells equal “Apple”, “Banana”, or “Cherry”.

Formula: =SUM(COUNTIF(A2:A20,{"Apple","Banana","Cherry"}))

In Excel 365 and Excel 2021, this works as a dynamic array formula—press Enter and it calculates automatically. In older Excel (2019 and earlier), you must press Ctrl+Shift+Enter to enter it as an array formula (curly braces appear around it). The result, for example, if there are 3 Apples, 2 Bananas, and 1 Cherry, would be 6.

To avoid array entry entirely, wrap it in SUMPRODUCT: =SUMPRODUCT(COUNTIF(A2:A20,{"Apple","Banana","Cherry"}))—this works in all versions without special key presses.

Method 2: Using SUMPRODUCT with ISNUMBER and MATCH

If your list of values is stored in cells (not typed as an array constant), you can use a more flexible formula. Suppose your values are in D2:D4 (Apple, Banana, Cherry).

Formula: =SUMPRODUCT(--ISNUMBER(MATCH(A2:A20,D2:D4,0)))

How it works: MATCH checks each cell in A2:A20 against the list in D2:D4, returning a number if found, or #N/A if not. ISNUMBER converts that to TRUE/FALSE, the double negative (--) coerces TRUE/FALSE to 1/0, and SUMPRODUCT sums them. This method is easier to maintain because you can edit the list in cells without changing the formula.

Method 3: Using COUNTIFS with OR Logic (Alternative)

You might think COUNTIFS can count multiple values, but it actually applies AND logic across different ranges. For example, =COUNTIFS(A2:A20,"Apple",B2:B20,"Red") counts rows where column A is “Apple” AND column B is “Red”. To count multiple values in the same column, you still need to add multiple COUNTIFS together:

=COUNTIFS(A2:A20,"Apple",B2:B20,"Red")+COUNTIFS(A2:A20,"Banana",B2:B20,"Red")

This is useful when you have additional criteria, but for a single column with multiple values, the SUM method is cleaner.

Common Problems and Misconceptions

  • Wildcards: COUNTIF supports wildcards like * and ?. For instance, =COUNTIF(A2:A20,"A*") counts cells starting with “A”. When using multiple values, you can combine wildcards: =SUM(COUNTIF(A2:A20,{"A*","B*"})).
  • Case sensitivity: COUNTIF is not case-sensitive. “apple” and “APPLE” are treated the same. If you need case-sensitive counting, you’d have to use SUMPRODUCT with EXACT.
  • Blank cells: If your range includes blanks, they won’t match any text value, but they will match the criteria "" (empty string) if you include it.
  • Array constant limits: In older Excel, array constants can’t exceed 255 characters total, so keep your list short. For longer lists, use the cell range method.
  • Forgetting Ctrl+Shift+Enter: In Excel 2019 and earlier, if you don’t enter the SUM(COUNTIF(…)) as an array formula, it returns only the count for the first value. Always use SUMPRODUCT to avoid this issue.

How to Choose the Right Method

Consider your version and how often you’ll change the criteria:

  • If you use Excel 365 or 2021, the dynamic array SUM(COUNTIF(...)) is simplest—just press Enter.
  • If you use Excel 2019 or earlier, use SUMPRODUCT(COUNTIF(...)) to avoid array entry.
  • If your criteria are in cells, use the SUMPRODUCT(--ISNUMBER(MATCH(...))) method for easier updates.
  • If you have additional conditions on other columns, use multiple COUNTIFS added together.

Real-World Example with Step-by-Step

Let’s say you have a sales log in columns A (Product) and B (Region). You want to count how many sales were for either “Laptop” or “Tablet” in the “East” region.

  1. Put your product list in cells D2:D3 (Laptop, Tablet).
  2. In cell E2, enter: =SUMPRODUCT((A2:A100="Laptop")+(A2:A100="Tablet"))*(B2:B100="East")
  3. Press Enter. This counts rows where column A is Laptop or Tablet, and column B is East.

Alternatively, using COUNTIFS: =COUNTIFS(A2:A100,"Laptop",B2:B100,"East")+COUNTIFS(A2:A100,"Tablet",B2:B100,"East").

Both give the same result. The SUMPRODUCT version is shorter but can be harder to read; the COUNTIFS version is clearer but longer.

Frequently Asked Questions

Can COUNTIF count multiple criteria in the same range?

No, a single COUNTIF only accepts one criterion. To count multiple values in the same range, you must combine multiple COUNTIF functions or use SUMPRODUCT.

Why does my SUM(COUNTIF(…)) return only the first value?

This happens in Excel 2019 and earlier when you don’t press Ctrl+Shift+Enter. The formula is evaluated as a regular formula, returning the first COUNTIF result. Use SUMPRODUCT to avoid this, or upgrade to Excel 365.

Can I use a cell reference for the list of values?

Yes, but not directly inside COUNTIF’s array constant. Use the SUMPRODUCT(--ISNUMBER(MATCH(range, list_range, 0))) method, where list_range is a range of cells containing your values.

How do I count cells that contain any of several text fragments?

Use wildcards with COUNTIF: =SUM(COUNTIF(A2:A20,{"*apple*","*banana*"})). This counts cells containing “apple” or “banana” anywhere in the text.

Does COUNTIF work with numbers and dates?

Yes, you can use numbers and dates as criteria. For example, =SUM(COUNTIF(A2:A20,{1,2,3})) counts cells equal to 1, 2, or 3. For dates, use DATE(2025,1,1) inside the array constant.

Is there a way to count multiple values without using an array formula?

Yes, the SUMPRODUCT wrapper around COUNTIF works without array entry. Also, you can use multiple COUNTIF functions added together, like =COUNTIF(A2:A20,"Apple")+COUNTIF(A2:A20,"Banana"), which is simple but gets long with many values.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.