Using OR & AND in COUNTIFS

In Excel, the COUNTIFS function is used to count rows that meet multiple criteria, but it does not natively support OR logic directly within its criteria. You can, however, combine COUNTIFS with other functions to implement both AND and OR logic. Here’s how:

1. AND Logic in COUNTIFS

When you want all criteria to be true (AND logic), COUNTIFS works directly. Each condition is treated as “must meet this AND that.”

Syntax:

=COUNTIFS(range1, criteria1, range2, criteria2, ...)

Example:

Count rows where:

• A1:A10 has “Apple”
• B1:B10 has “Red”

=COUNTIFS(A1:A10, "Apple", B1:B10, "Red")

This counts rows where column A contains “Apple” AND column B contains “Red.”

2. OR Logic in COUNTIFS

To implement OR logic (e.g., condition1 OR condition2), you need to add multiple COUNTIFS functions together.

Example:

Count rows where:

• A1:A10 has “Apple”
• OR A1:A10 has “Banana”

=COUNTIFS(A1:A10, "Apple") + COUNTIFS(A1:A10, "Banana")

This adds the counts of “Apple” and “Banana” in column A.

3. Combining AND and OR Logic

If you need a mix of AND and OR logic, combine COUNTIFS with addition or other functions.

Example:

Count rows where:

• A1:A10 has “Apple” AND B1:B10 has “Red”
• OR
• A1:A10 has “Banana” AND B1:B10 has “Yellow”

=COUNTIFS(A1:A10, "Apple", B1:B10, "Red") + COUNTIFS(A1:A10, "Banana", B1:B10, "Yellow")

4. Advanced: Array Formula for OR Logic

For more complex OR conditions over the same range, you can use an array formula (with SUM and COUNTIFS) or SUMPRODUCT.

Example:

Count rows where:

• A1:A10 has “Apple” OR “Banana”

=SUM(COUNTIFS(A1:A10, {"Apple", "Banana"}))

• {} is an array for multiple criteria.
• Use Ctrl+Shift+Enter to evaluate as an array formula in older Excel versions (not needed in Excel 365).

Summary:

• Use COUNTIFS for AND logic directly.
• Use multiple COUNTIFS combined with + for OR logic.
• Use arrays ({}) for advanced OR logic over the same range.

Let me know if you’d like tailored examples!