Excel COUNTIF and COUNTIFS with OR logic

This tutorial demonstrates how to use Excel's COUNTIF and COUNTIFS functions to count cells meeting multiple OR conditions (e.g., if a cell contains X, Y, or Z).

While COUNTIF handles single criteria and COUNTIFS uses AND logic, this tutorial provides solutions for OR logic scenarios. It assumes familiarity with the basic syntax and usage of both functions.

Counting Cells with OR Conditions

The simplest case involves counting cells satisfying at least one of several conditions.

Method 1: COUNTIF COUNTIF

This adds the results of individual COUNTIF functions for each criterion. For example, to count cells in column A containing "apples" or "bananas":

=COUNTIF(A:A, "apples") COUNTIF(A:A, "bananas")

Using cell references (e.g., criteria in F1 and G1) improves efficiency and maintainability:

=COUNTIF(A2:A10, F1) COUNTIF(A2:A10, G1)

Method 2: COUNTIF with Array Constant

This offers a more compact approach:

SUM(COUNTIF(*range*, {*criterion1*, *criterion2*, *criterion3*, …}))

The criteria are enclosed in an array constant {"apples","bananas","lemons"}. COUNTIF returns multiple counts, which SUM adds.

=SUM(COUNTIF(A2:A10,{"apples","bananas","lemons"}))

Using cell references (F1:H1) requires an array formula (Ctrl Shift Enter):

=SUM(COUNTIF(A2:A10,F1:H1))

Method 3: SUMPRODUCT

SUMPRODUCT(1\*(*range* ={*criterion1*, *criterion2*, *criterion3*, …})) or SUMPRODUCT((*range*=*criterion1*) (*range*=*criterion2*) …)

This tests each cell against each criterion, summing the resulting TRUE/FALSE array (converted to 1/0).

=SUMPRODUCT(1*(A2:A10={"apples","bananas","lemons"}))

or

=SUMPRODUCT((A2:A10="apples") (A2:A10="bananas") (A2:A10="lemons"))

Using cell references (F1:H1):

=SUMPRODUCT(1*( A2:A10=F1:H1))

Note: SUMPRODUCT can be slower than COUNTIF for large datasets.

Counting Cells with OR and AND Logic

This extends the concept to include both OR and AND conditions. For example, counting "apples," "bananas," or "lemons" that are "delivered" (column A and column C):

Method 1: COUNTIFS COUNTIFS

This adds multiple COUNTIFS functions, each checking one OR condition and the AND condition.

=COUNTIFS(A2:A10, "apples", C2:C10, "delivered") COUNTIFS(A2:A10, "bananas", C2:C10, "delivered") COUNTIFS(A2:A10, "lemons", C2:C10, "delivered")

Method 2: COUNTIFS with Array Constant

A more concise version using an array constant for OR criteria:

=SUM(COUNTIFS(A2:A10, {"apples","bananas","lemons"}, C2:C10, "delivered"))

With cell references (F1:H1 and F2), an array formula (Ctrl Shift Enter) is needed:

=SUM(COUNTIFS(A2:A10,F1:H1,C2:C10,F2))

Wildcards are supported (e.g., "*bananas*"). Additional AND conditions can be added to COUNTIFS.

Counting Cells with Multiple OR Conditions

For multiple sets of OR criteria, COUNTIFS with array constants (limited to two sets) or SUMPRODUCT with ISNUMBER and MATCH (handles more sets) are used.

For two sets, use horizontal and vertical arrays within COUNTIFS:

=SUM(COUNTIFS(A2:A10, {"apples", "bananas", "lemons"}, B2:B10, {"delivered"; "in transit"}))

For multiple sets, SUMPRODUCT with MATCH is more versatile:

=SUMPRODUCT(ISNUMBER(MATCH(A2:A10,{"apples","bananas","lemons"},0))*ISNUMBER(MATCH(B2:B10,{"bag","tray"},0))*ISNUMBER(MATCH(C2:C10,{"delivered","in transit"},0)))

This tutorial provides various methods for handling OR conditions in Excel's counting functions, catering to different complexity levels and dataset sizes. A sample workbook is available for further practice.

The above is the detailed content of Excel COUNTIF and COUNTIFS with OR logic. For more information, please follow other related articles on the PHP Chinese website!