In Excel for Microsoft 365, COUNTIFS can evaluate an array of criteria and return a separate count for each item, usually spilling the results into neighboring cells. For example, a region array can count East and West records in one formula; wrap the result in SUM if you want one combined total.
What an array means in COUNTIFS
COUNTIFS counts records that meet multiple conditions. Its ordinary use is straightforward: give it a range to check, a criterion for that range, and then additional range-and-criterion pairs. An array adds several criteria values to one of those criterion positions. Instead of checking only whether a region is East, for instance, Excel can check East and West and return two counts.
That distinction matters because an array of criteria is not the same thing as an array of data. The data still comes from worksheet ranges, such as a column of regions. The criteria array supplies several tests for COUNTIFS to apply to those ranges. In current Excel, the individual results can spill into adjacent cells as a dynamic array.
COUNTIFS applies different range-and-criterion pairs with AND logic to each result. If a formula checks for a particular region and a particular category, a row must match both. An array in one criterion position changes that one test into several separate calculations; it does not automatically make every condition an OR test.
COUNTIFS syntax and range requirements
The function’s syntax is COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...). Each criterion range is paired with its own criterion. Criteria can be text, numbers, comparison expressions, cell references, or arrays of values. For a comparison such as greater than 100, put the operator and number together in a quoted criterion.
All criteria ranges must have the same number of rows and columns and should describe corresponding records. If the region range covers rows 2 through 8, the category and sales ranges should cover those same rows. Mismatched ranges can return a result that is wrong for the intended records or an error, so use consistent endpoints.
COUNTIFS expects range references for its criteria ranges. It is not a general-purpose function for transforming each cell in a range before counting it. For example, a calculated expression such as taking the first character of every value is not a direct replacement for a criteria range. Use a suitable wildcard criterion, a helper column, or another counting approach when the test requires a transformation.
Enter an array of criteria
Suppose a worksheet has Region in A2:A8, Category in B2:B8, and Sales in C2:C8. The seven records are East/Hardware/120, West/Hardware/90, East/Services/150, North/Hardware/80, West/Services/110, East/Hardware/95, and West/Hardware/130. With region names in A2:A8, this formula tests two region criteria:
=COUNTIFS(A2:A8,{"East","West"})
In Microsoft 365 for Windows, the two results spill horizontally: 3 for East and 3 for West. The comma inside the array constant separates its two items, so Excel evaluates the COUNTIFS calculation once for each item. Leave the cells to the right of the formula empty, or Excel will show a spill error because it cannot place the full result there.
You can use a worksheet range instead of typing the criteria into the formula. With region names East and West in E2:E3, and the data still in A2:A8, this formula returns two results vertically:
=COUNTIFS(A2:A8,E2:E3)
Referencing criteria cells is useful when the list changes or is longer than a few items. It also makes the intended inputs visible on the sheet. Keep the criteria list clear of the cells where the results need to spill.
Count multiple conditions for each array item
Array criteria can work alongside other conditions. With regions in A2:A8 and categories in B2:B8, this formula counts Hardware records separately for East and West:
=COUNTIFS(A2:A8,{"East","West"},B2:B8,"Hardware")
The result is 2 for East and 2 for West. In the sample data, East has two Hardware rows and West has two. The category condition is applied to each region calculation, so this is effectively two counts: East plus Hardware, and West plus Hardware.
You can also use a criteria range for the region values. With East and West in E2:E3, and the same data layout, enter:
=COUNTIFS(A2:A8,E2:E3,B2:B8,"Hardware")
Excel returns the corresponding counts vertically beside the criteria list, provided the output cells are available. This arrangement is often easier to maintain in a report because adding another region to the criteria list can extend the calculation without editing an array constant inside the formula.
Choose between separate counts and one total
Use the spilled array when you need a breakdown, such as a count for each region. If the question is “How many records are from East or West altogether?”, add the individual results with SUM. With regions in A2:A8, the formula below returns 6 because there are three East records and three West records:
=SUM(COUNTIFS(A2:A8,{"East","West"}))
When criteria can overlap, be careful about what a combined total means. If a record can match more than one criterion, adding separate counts can count that record more than once. East and West are mutually exclusive labels in the sample, so adding their counts is safe. For overlapping tests, define whether you want the sum of matches or the number of unique records matching at least one condition.
There is another common distinction: putting two different criteria on the same range in separate COUNTIFS pairs creates an AND condition, not an OR. A cell cannot ordinarily equal both East and West at once. To count either value, use an array of alternatives and, for a single total, sum its results.
Dynamic arrays and older Excel versions
Microsoft 365 supports dynamic arrays, so a formula that returns several results can spill into nearby cells without being entered as a special array formula. On Windows, enter the formula in the first output cell and press Enter. Excel outlines a spilled range when the calculation returns multiple values. You edit the formula in its top-left cell; the other cells display its results.
Older Excel versions do not have the same automatic spill behavior. Array calculations may need to be entered as legacy array formulas with Ctrl+Shift+Enter, and behavior can vary by version and formula construction. If a workbook must work across different Excel releases, test it in the oldest target version rather than assuming that a dynamic-array formula will behave identically everywhere.
On Excel for the web, dynamic array formulas are supported, although the ribbon and editing experience differ from the Windows desktop application. On Excel for Mac, Microsoft 365 also supports dynamic arrays; the main practical difference is the keyboard shortcut for commands such as entering legacy array formulas. The formulas shown here use standard comma-separated argument syntax. Some regional settings use different list separators, so Excel may display or require a localized separator.
Common problems and how to fix them
- A #SPILL! error: Clear cells in the intended output area, including cells that look blank but contain spaces or formulas. Also check for merged cells blocking the spill range.
- Unexpected zero: Check spelling, leading or trailing spaces, number-versus-text differences, and the exact range boundaries. A criterion containing “East” will not match a value with an extra space unless the criterion accounts for it.
- Only one result appears: Confirm that the criteria argument really contains multiple values and that the Excel version supports dynamic arrays. In older versions, try an appropriate legacy array entry or calculate each count separately.
- Counts seem duplicated: If you sum results for alternatives, verify whether one row can satisfy more than one alternative. Overlapping criteria can make a sum larger than the number of distinct matching rows.
- Wrong rows are counted: Make sure every criteria range covers the same record rows. Avoid including a header in one range but not the others.
- Wildcards match more than expected: In COUNTIFS, an asterisk represents any sequence of characters and a question mark represents one character. To search for a literal wildcard, prefix it with a tilde.
For a prefix test, wildcards can often avoid the need to build an array from transformed text. With product names in D2:D20, this example counts names beginning with “Pro”:
=COUNTIFS(D2:D20,"Pro*")
The asterisk allows any characters after the prefix. This is different from testing an exact match, and it will include values such as “Product” and “Pro Tools.”
When to use SUMPRODUCT or a helper column
COUNTIFS is usually the clearest option when the conditions can be expressed as ordinary ranges and criteria. Use SUMPRODUCT or a helper column when the test depends on calculations over the data, such as examining part of each text value or combining more complex row-level logic. A helper column is often the easiest choice to audit: calculate a flag or cleaned value once, then count that column with COUNTIFS.
SUMPRODUCT can evaluate array expressions directly, but its formulas can be harder to read and may require more care with data types and range sizes. It is not automatically better just because a task mentions arrays. Prefer COUNTIFS for straightforward criteria, especially when the workbook should be understandable to other spreadsheet users.
For example, if the goal is to count entries whose first character is E, a helper column that extracts or records that character lets COUNTIFS use a normal range. If the goal is simply to count values that start with East, a wildcard criterion is more direct. Choose based on the actual rule, not on the word “array” alone.
Frequently Asked Questions
Can COUNTIFS use more than one criterion at once?
Yes. Add a range-and-criterion pair for each additional condition. COUNTIFS requires all those conditions to be true for a record to count.
How do I count either of two values with COUNTIFS?
Put the alternatives in an array criterion to get a count for each value. To produce one combined total, sum those results, while checking first that the alternatives do not overlap.
Why does my COUNTIFS array formula show #SPILL!?
Excel cannot place one or more returned values in the needed cells. Clear the blocked cells or move the formula to an area with enough empty space.
Can I use a cell range as the criteria array?
Yes. In Microsoft 365, a criteria range such as E2:E5 can produce a corresponding spilled count for each listed criterion. Make sure the results have room to spill.
Does COUNTIFS treat criteria as OR conditions?
Not by default. Separate range-and-criterion pairs are combined with AND. An array of alternatives lets Excel evaluate multiple possibilities separately; use SUM when you need their total.
Can COUNTIFS count part of a text value?
Yes, for common starts-with, ends-with, or contains checks, use wildcard criteria. For more involved text transformations, consider a helper column or another array-capable function.
Related
- Excel Data Model and SharePoint: How They Work
- Excel Data Model on Mac: What Works and What Doesn’t
- Excel Data Model Relationships: How They Work in Reports
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel LAMBDA Function: Create and Reuse Custom FormulasTo use Excel’s LAMBDA function, define a calculation with named inputs, save it as a workbook name, and…
- PowerPoint Poster Slide Size: Set the Right DimensionsTo change a PowerPoint slide to poster size in Microsoft 365 for Windows, select Design > Slide Size…
- Excel Autofill Keyboard Shortcuts for Fill Down and RightTo autofill a selected range in Excel, include the source cell and the cells you want to fill,…
- Copy Formula in Excel Without Dragging: 6 WaysTo copy a formula in Excel without dragging, copy and paste it, use Ctrl+D to fill down, or…
- Excel HLOOKUP: Return a Column NumberHLOOKUP does not return the number of the column where it finds a match; it returns a value…
- Excel Formula for Percentage Increase: Examples and TipsTo calculate percentage increase in Excel, subtract the old value from the new value, then divide by the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.