Yes, Excel’s COUNTIFS function can match multiple criteria across different columns (or the same column) using AND logic—all conditions must be true for a row to be counted. For example, =COUNTIFS(A2:A10,"Red",B2:B10,">10") counts rows where column A is “Red” AND column B is greater than 10.
What COUNTIFS Does and When to Use It
COUNTIFS is a statistical function that counts the number of cells in a range that meet one or more conditions. It was introduced in Excel 2007 and works in all later versions, including Excel for Microsoft 365, Excel 2021, Excel 2019, and Excel for the web. The syntax is:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
You can pair up to 127 range/criteria pairs in modern Excel. Each range must have the same number of rows and columns, but they do not need to be adjacent. The function uses AND logic: every condition you add must be true for that row to be included in the count. If you need OR logic (count if any one of several conditions is true), you’ll need a different approach—usually using SUMPRODUCT or adding multiple COUNTIFS together.
How to Write a COUNTIFS Formula with Multiple Criteria
Let’s walk through a concrete example. Suppose you have a sales table with:
- Product names in column A (A2:A20)
- Region in column B (B2:B20)
- Sales amount in column C (C2:C20)
You want to count how many rows are for “Widget” in the “North” region with sales over $500. The formula would be:
=COUNTIFS(A2:A20,"Widget",B2:B20,"North",C2:C20,">500")
If your data has 3 Widget rows in North with sales 400, 600, and 700, this formula returns 2 (only the 600 and 700 rows meet all three conditions).
You can also use cell references for criteria, which makes the formula easier to update. For instance, with “Widget” in E1, “North” in E2, and 500 in E3:
=COUNTIFS(A2:A20,E1,B2:B20,E2,C2:C20,">"&E3)
Note the ampersand (&) to join the greater-than symbol with the cell reference. This works because COUNTIFS expects a text string for criteria, and ">"&E3 produces “>500”.
Using Wildcards for Partial Matches
COUNTIFS supports wildcards in criteria: the asterisk (*) matches any sequence of characters, and the question mark (?) matches any single character. For example, to count all products that start with “Widget” (like “Widget Pro” or “Widget Mini”), use:
=COUNTIFS(A2:A20,"Widget*")
To count rows where the product name contains “Pro” anywhere, use "*Pro*". If you need to count an actual asterisk or question mark as a literal character, precede it with a tilde (~), like "~*".
Wildcards work in COUNTIFS just as they do in COUNTIF, and they apply to text criteria only—not to numeric comparisons.
Common Problems and How to Fix Them
Several issues trip up users new to COUNTIFS:
- Mismatched range sizes: All ranges must have the same number of rows and columns. If you use A2:A20 and B2:B21, Excel returns a #VALUE! error.
- Criteria with quotes: Text criteria must be enclosed in double quotes, like
"North". Numeric criteria without operators (like500) do not need quotes, but if you use an operator, the whole expression must be in quotes:">500". - Blank cells: To count blanks, use
""as the criteria. To count non-blanks, use"<>"(which means “not equal to nothing”). Be careful:"<>"counts cells that contain any text or number, but not truly empty cells. - Dates as criteria: If you enter a date directly in the formula, use the DATE function to avoid regional date issues:
=COUNTIFS(A2:A20,">="&DATE(2025,1,1)). - Case sensitivity: COUNTIFS is not case-sensitive. “north” and “NORTH” are treated the same. If you need case-sensitive counting, use SUMPRODUCT with EXACT.
Alternatives: SUMPRODUCT, FILTER, and PivotTables
When COUNTIFS isn’t enough, consider these alternatives:
- OR logic: To count rows where column A is “Red” OR “Blue”, use
=COUNTIFS(A2:A10,"Red")+COUNTIFS(A2:A10,"Blue"). For more complex OR conditions across columns, use=SUMPRODUCT((A2:A10="Red")+(B2:B10="Blue"))—but note that this counts rows where either condition is true, not necessarily both. - Case-sensitive counting: Use
=SUMPRODUCT(--EXACT(A2:A10,"Red"))to count only exact “Red” (uppercase R) and not “red”. - Counting unique values that meet criteria: COUNTIFS cannot count unique items. Use
=SUM(--(FREQUENCY(IF(condition,MATCH(range,range,0)),ROW(range)-ROW(first_cell)+1)>0))as an array formula (enter with Ctrl+Shift+Enter in older Excel, or just Enter in Excel 365). - FILTER function (Excel 365 and 2021): To see the actual rows that match, use
=FILTER(A2:C20,(A2:A20="Widget")*(B2:B20="North")*(C2:C20>500)). This returns the matching rows, and you can wrap it in=ROWS(FILTER(...))to get a count. - PivotTables: For quick counts with multiple criteria, drag fields to the Rows and Values areas. This is often easier for ad-hoc analysis but not as dynamic as formulas.
Performance and Large Data Sets
COUNTIFS is generally fast, but on very large datasets (hundreds of thousands of rows) it can slow down if used in thousands of cells. For better performance, consider using a helper column with a formula like =AND(A2="Widget",B2="North",C2>500) and then count TRUE values with =COUNTIF(D2:D100000,TRUE). Or use a PivotTable, which is optimized for large data. In Excel 365, the FILTER function is also efficient, but it recalculates each time the data changes.
Frequently Asked Questions
Can COUNTIFS count across multiple sheets? Yes, but each criteria_range must be a single contiguous range on one sheet. You cannot reference non-contiguous ranges or multiple sheets in one COUNTIFS. To count across sheets, use multiple COUNTIFS and add them, or use SUMPRODUCT with INDIRECT (but INDIRECT is volatile and can slow down your workbook).
How do I count if a cell contains a specific text, like “apple” anywhere? Use wildcards: =COUNTIFS(A2:A10,"*apple*"). This counts cells that contain “apple” as a substring, regardless of surrounding text.
Why does my COUNTIFS return 0 when I know there are matches? Check for extra spaces in your data. For example, “North ” with a trailing space won’t match “North”. Use the TRIM function to clean data, or use wildcards like "North*" to ignore trailing characters. Also verify that your ranges are the same size and that numeric criteria are formatted correctly.
Can I use a cell reference for the criteria range itself? Yes, you can use a named range or a cell reference that contains a range, like =COUNTIFS(INDIRECT("A2:A20"),"Red"). However, INDIRECT is volatile and recalculates whenever any cell changes, which can slow down large workbooks. Prefer direct references.
Is there a way to count with OR logic inside COUNTIFS? No, COUNTIFS only does AND logic. For OR logic, either add multiple COUNTIFS together or use SUMPRODUCT. For example, to count rows where column A is “Red” or column B is “Blue”, use =SUMPRODUCT((A2:A10="Red")+(B2:B10="Blue"))—but be careful: this counts rows where both conditions are true twice, so you may need to subtract double-counted rows.
Does COUNTIFS work in Google Sheets? Yes, Google Sheets has the same COUNTIFS function with identical syntax. The main difference is that Google Sheets uses semicolons (;) as argument separators in some locales, but in the US English locale it uses commas. Also, wildcards work the same, but case-insensitivity is the same.
Related
- Excel Calendar Template with Notes? How to Make One
- Excel Calendar Template for Content Planning
- Excel Calendar Template for Events: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Percentage of a NumberTo calculate a percentage of a number in Excel, multiply the number by the percentage: if A2 contains…
- Excel INDIRECT Errors: Causes, Fixes, and ExamplesAn Excel INDIRECT error usually means the text supplied to INDIRECT cannot be turned into a valid cell…
- Excel Shortcut for Removing a Filter: What WorksIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter off or on; this removes the…
- Packing List Excel With Formula: Build a Smart ChecklistYes. In an Excel packing list, put each item in column A and its packing status in column…
- How to Compare Two Columns in Excel With a FormulaTo compare two Excel columns row by row, enter =IF(A2=B2,"Match","Different") in C2, then fill the formula down. This…
- Excel COUNTIF: Find Text From a ListTo count cells that contain any text from a list in Excel for Microsoft 365, use =SUM(COUNTIF(A2:A10,"*"&C2:C4&"*")), where…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.