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
COUNTIFSadded 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.
- Put your product list in cells D2:D3 (Laptop, Tablet).
- In cell E2, enter:
=SUMPRODUCT((A2:A100="Laptop")+(A2:A100="Tablet"))*(B2:B100="East") - 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
- Excel Audit Log Template: How to Build One
- Excel Auto Loan Amortization Template: How to Build One
- Excel Attendance Point Tracker Template: Setup & Use
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Filter Shortcut: Ctrl+Shift+L and How to Use ItIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter on or off for a selected…
- Excel Named Ranges in Formulas: How to Use ThemYes. In Excel for Microsoft 365, type a defined name into a formula anywhere you would otherwise use…
- Excel Sheet Number Sequence: How to Create OneTo create a number sequence in an Excel worksheet, put =SEQUENCE(10) in a blank cell with room below…
- XLOOKUP Multiple Criteria: Examples and Common FixesTo use XLOOKUP with multiple criteria in Excel, multiply the criteria tests and look for 1: =XLOOKUP(1,(A2:A100=H2)*(B2:B100=H3),C2:C100,"Not found").…
- Excel Formula for Tomorrow’s Date: TODAY()+1 ExplainedIn Excel, use =TODAY()+1 to return tomorrow’s date. Enter it in a cell and format that cell as…
- Dynamic Drop-Down Lists in Excel Using INDIRECT: How-ToYes. To make a dependent drop-down in Excel, create a named range for each list, then set the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.