Using OR & AND in COUNTIFS

Mastering COUNTIFS with OR and AND Logic in Excel

Learn how to combine COUNTIFS with OR and AND conditions to perform complex data analysis and count cells based on multiple criteria in Excel.

Excel's COUNTIFS function is incredibly powerful for counting cells that meet multiple criteria. However, it inherently operates with an 'AND' logic, meaning all specified conditions must be true for a cell to be counted. This article will guide you through techniques to introduce 'OR' logic into your COUNTIFS formulas, allowing for more flexible and sophisticated conditional counting.

Understanding COUNTIFS Basics (AND Logic)

The COUNTIFS function counts the number of cells within a range that meet multiple criteria. Each criterion is applied with an implicit 'AND' operator. For example, to count sales greater than $100 AND in the 'East' region, you would use a standard COUNTIFS formula.

=COUNTIFS(Sales_Range, ">100", Region_Range, "East")

Basic COUNTIFS with implicit AND logic

flowchart TD
    A["Start COUNTIFS"]
    B["Check Criterion 1 (Sales > 100)"]
    C["Check Criterion 2 (Region = 'East')"]
    D{"Both True?"}
    E["Increment Count"]
    F["End"]

    A --> B
    B --> C
    C --> D
    D -- "Yes" --> E
    D -- "No" --> F
    E --> F

Flowchart of COUNTIFS with AND logic

Implementing OR Logic with COUNTIFS

Since COUNTIFS doesn't directly support 'OR' conditions between different criteria, you need to employ workarounds. The most common method involves summing multiple COUNTIFS functions, each representing one part of the 'OR' condition. Another powerful technique uses array constants within COUNTIFS.

Method 1: Summing Multiple COUNTIFS (OR Logic)

This method is straightforward: write a separate COUNTIFS formula for each 'OR' condition and then sum their results. This works well when you want to count items that meet Condition A OR Condition B, where Condition A and B are distinct sets of criteria.

=COUNTIFS(Region_Range, "East", Sales_Range, ">100") + COUNTIFS(Region_Range, "West", Sales_Range, ">100")

Counting sales > $100 in 'East' OR 'West' regions

Method 2: Using Array Constants for OR Logic within a Single COUNTIFS

For 'OR' conditions applied to a single criterion range, you can use an array constant. This method is more compact and often preferred. Excel will evaluate the COUNTIFS for each item in the array constant and then sum the results. This is particularly useful when checking if a cell contains one of several specific values.

=SUM(COUNTIFS(Region_Range, {"East","West"}, Sales_Range, ">100"))

Counting sales > $100 in 'East' OR 'West' regions using an array constant

flowchart TD
    A["Start SUM(COUNTIFS(...))"]
    B["COUNTIFS for 'East' & '>100'"]
    C["COUNTIFS for 'West' & '>100'"]
    D["Sum Results"]
    E["Final Count"]

    A --> B
    A --> C
    B --> D
    C --> D
    D --> E

Flowchart of COUNTIFS with array constant for OR logic

Combining AND and OR Logic

You can combine both AND and OR logic by nesting these techniques. For example, to count items that are in the 'North' region AND (Category 'A' OR Category 'B'), you would use the array constant method for the 'OR' part, combined with the 'AND' part of COUNTIFS.

=SUM(COUNTIFS(Region_Range, "North", Category_Range, {"A","B"}))

Counting items in 'North' region AND (Category 'A' OR 'B')