Excel’s COUNTIF function is a powerful tool for counting cells that meet a given criterion. But what if you need to check multiple criteria simultaneously? That’s where understanding both COUNTIF and COUNTIFS comes in. In this blog post, we’ll explore how to use them to handle both OR logic (any of multiple criteria) and AND logic (all criteria), along with some pro tips and examples.
Understanding COUNTIF vs. COUNTIFS
- COUNTIF allows you to specify one condition: At its core, COUNTIF counts the number of cells in a range that match a single condition (criterion).
=COUNTIF(A2:A10, "Apples")
For example: This formula counts how many times “Apples” appears in the range A2:A10. That’s straightforward enough. But in real life, you often need to check more than one condition.
- COUNTIFS extends this to multiple conditions (using AND logic): Whereas COUNTIF can handle only one criterion, COUNTIFS allows you to evaluate multiple conditions using AND logic.
=COUNTIFS(A2:A10, "Fruit", B2:B10, ">10")
For instance: This counts the number of rows where column B contains “Fruit” AND column C has a value greater than 10. Each pair of range and criterion acts as a separate condition.
This difference is crucial when deciding how to handle multiple criteria.
Excel COUNTIF Multiple Criteria
Here are the Excel formulas to count cells based on multiple criteria. Whether you need to apply OR logic, AND logic, or a combination of both, the examples below will help you accurately count matching cells in your data. From traditional COUNTIF/COUNTIFS techniques to using the modern FILTER function, this guide has you covered.
Multiple Criteria with OR Logic
When you want to count cells that meet any of several conditions (e.g., “Apples” OR “Bananas”), you typically have two choices:
- Using an Array Constant with SUM
=SUM(COUNTIF(A2:A10, {"Apples","Bananas"}))
This single formula counts how many times “Apples” or “Bananas” appear in A2:A10.
- Adding Multiple COUNTIF Functions
=COUNTIF(A2:A10, "Apples") + COUNTIF(A2:A10, "Bananas")
Both approaches accomplish the same task.
Tip: This OR logic trick can be extended to more than two items by adding additional elements in the array constant or more COUNTIF functions.
Multiple Criteria with AND Logic
For counting cells that meet all conditions (e.g., “Fruit” AND Quantity > 10), use COUNTIFS:
=COUNTIFS(B2:B10, "Fruit", C2:C10, ">10")
- B2:B10, “Fruit” means column B must be “Fruit.”
- C2:C10, “>10” means column C must have a value greater than 10.
Every additional pair of range, criteria applies another AND constraint.
Combining OR and AND in One Formula
Sometimes you want to combine OR and AND. For instance, count: (Fruit AND Quantity > 10) OR (Vegetable AND Quantity > 20)
You can break it into two AND clauses and sum the results:
=COUNTIFS(A2:A10, "Fruit", B2:B10, ">10") + COUNTIFS(A2:A10, "Vegetable", B2:B10, ">20")
Each COUNTIFS handles one set of AND conditions, and the + creates an OR scenario between them.
Excel COUNTIF Multiple Criteria Different Column
When each criterion is in a different column, you’ll likely use COUNTIFS. For example, count rows where Column A contains “Fruit” and Column B has “USA”:
=COUNTIFS(A2:A100, "Fruit", B2:B100, "USA")
This formula only counts rows if both conditions are met in different columns.
Excel COUNTIF Multiple Criteria Same Column
If you want to check multiple conditions within the same column using AND logic, it can be tricky because a single cell can’t be both “Apple” and “Banana.” This scenario typically implies OR logic in the same column:
=SUM(COUNTIF(A2:A100, {"Apples","Bananas"}))
However, if your logic is truly “same column, AND,” you’re essentially checking for strings that contain multiple elements (like “Apples & Bananas”), which is not typical in a single cell unless you’re using wildcards or partial text searches.
COUNTIF Multiple Criteria Same Range
To check multiple criteria in one range (often with OR), the simplest approach is the array constant or summing multiple COUNTIF results. For example, if you need to count how many times the range A2:A10 has either “Red,” “Green,” or “Blue”:
=SUM(COUNTIF(A2:A10, {"Red","Green","Blue"}))
This single formula effectively counts any cell matching one of those three colors.
COUNTIF Multiple Ranges
If you want to count occurrences of a criterion across multiple separate ranges, you can add individual COUNTIFs. For instance, count how many times “Apples” appears in A2:A10 and C2:C10:
=COUNTIF(A2:A10, "Apples") + COUNTIF(C2:C10, "Apples")
If the ranges are the same size, you could also explore combining them in more advanced formulas, but typically adding the results is the easiest approach.
INDEX MATCH COUNTIF Multiple Criteria
Sometimes you need more advanced lookups, like combining INDEX and MATCH with COUNTIF or COUNTIFS to handle multiple criteria. For example, suppose you have a dynamic criterion stored in a cell and need to count how many matching rows appear:
- MATCH to find which row/column your criterion belongs to.
- INDEX to retrieve the value.
- COUNTIF or COUNTIFS to do the final count.
A simplified pattern might look like:
=COUNTIF(A2:A100, INDEX($B$1:$B$5, MATCH(D1, $A$1:$A$5, 0)))
- MATCH finds the position of D1 within A1:A5.
- INDEX uses that position to retrieve a corresponding lookup value from B1:B5.
- COUNTIF uses that value as its criterion to count matches in A2:A100.
This approach is especially handy when your criterion depends on lookup results or user-driven selections.
Using FILTER Function for Multiple Criteria (AND Condition)
The FILTER function (available in modern Excel) can also help with AND logic. It returns only the rows that match all specified conditions, and then you can count those rows with COUNTA. For example, this filters rows where Column B = “Fruit” AND Column C > 10, then counts them:
=COUNTA(FILTER(A2:A11, (B2:B11="Fruit")*(C2:C11>10)))
- (B2:B11=”Fruit”) returns TRUE/FALSE for each cell in Column B.
- (C2:C11>10) returns TRUE/FALSE for each cell in Column C.
- Multiplying (B2:B11=”Fruit”) * (C2:C11>10) enforces AND logic (both must be TRUE).
- FILTER grabs only those rows from A2:A11.
- COUNTA counts how many cells were returned by FILTER.
Using FILTER Function for Multiple Criteria (OR Condition)
To apply OR logic with the FILTER function, replace the multiplication * with an addition +. For example, filter rows where Column B = “Fruit” OR Column C > 10:
=COUNTA(FILTER(A2:A11, (B2:B11="Fruit")+(C2:C11>10)))
Here, (B2:B11=”Fruit”) + (C2:C11>10) will be TRUE if either condition is satisfied. FILTER returns rows matching any of those conditions, and COUNTA tells you how many were returned.
Handling multiple criteria with COUNTIF and COUNTIFS is a core Excel skill that greatly simplifies data analysis. Whether you’re combining OR logic, AND logic, multiple ranges, or even integrating INDEX/MATCH, these techniques help you quickly answer questions about your data. Practice these formulas in different scenarios to become a true Excel counting master!
Handling multiple criteria with COUNTIF and COUNTIFS opens up a vast range of data analysis possibilities:
- OR logic: sum multiple COUNTIFs or use an array constant.
- AND logic: rely on COUNTIFS for straightforward multi-condition checks.
- Combining OR + AND: break conditions into separate COUNTIFS, then add (or nest) them.
- INDEX MATCH: perfect for dynamic lookups where your criteria come from user input or a lookup table.
- The FILTER function (available in newer Excel versions) can also handle AND or OR conditions, after which you can apply COUNTA to count the resulting rows.
Common Pitfalls to Avoid
- Order of Ranges and Criteria: In COUNTIFS, each range must be immediately followed by its criteria, and all ranges must have the same dimensions.
- Check for Extra Spaces: A common source of error is hidden or trailing spaces in your data.
- Case Sensitivity: COUNTIF and COUNTIFS are not case-sensitive. If you need case-sensitive matches, consider using a workaround like SUMPRODUCT(–EXACT(…)).
- Blank vs. Empty Strings: Counting blank cells can be tricky if some cells contain formulas returning “”. =COUNTIF(range, “”) will count cells with “” as well as truly empty cells.
Download Example File
Ready to put these formulas into practice? We’ve created an Excel workbook containing all the sample data and example formulas described in this guide. You can download it by clicking the link below:
Final Thoughts
Mastering COUNTIF and COUNTIFS for multiple criteria—whether OR logic, AND logic, or a combination—will vastly improve your data analysis skills in Excel. And if you’re using a newer version of Excel, the FILTER function provides an elegant way to handle multi-criteria filtering before counting. Explore these formulas, tweak them for your own datasets, and soon you’ll be tackling even the most complex counting tasks with ease.
Happy counting!

VERY informative and instantly applied successfully. Thanks everyone there.