COUNTIFS function

The COUNTIFS function in Excel counts the number of cells that meet multiple criteria across one or more ranges. Unlike COUNTIF, which handles a single condition, COUNTIFS allows for multiple conditions to be specified, and only cells that meet all conditions are counted.

Syntax:

COUNTIFS(range1, criteria1, [range2], [criteria2], ...)

Arguments:

• range1: The first range to evaluate.
• criteria1: The condition to apply to the first range.
• range2, range3, … (optional): Additional ranges to evaluate.
• criteria2, criteria3, … (optional): Additional conditions corresponding to each range.

Each range must have the same number of rows and columns, and the criteria can include numbers, text, logical expressions, or cell references.

Example 1: Counting with Multiple Criteria

Suppose you have the following data in two columns, where A1:A5 represents “Salesperson” and B1:B5 represents “Sales”:

To count how many sales made by John are greater than 150, you would use:

=COUNTIFS(A1:A5, "John", B1:B5, ">150")

This will return 2, as John made 200 and 300 sales, both greater than 150.

Example 2: Using Cell References for Criteria

You can also use cell references for dynamic conditions. For example, suppose:

• Cell C1 contains the name “John”
• Cell D1 contains the number 150

You can count the number of sales by John greater than the value in D1 using:

=COUNTIFS(A1:A5, C1, B1:B5, ">" & D1)

This will return 2, just like in the previous example, because John has 200 and 300 sales greater than 150.

Example 3: Counting Based on Date Ranges

If you have a range of dates in column C (e.g., C1:C5), and you want to count how many sales by Alice occurred after 2023-01-01, you would use:

=COUNTIFS(A1:A5, "Alice", C1:C5, ">2023-01-01")

Key Points:

• COUNTIFS can handle multiple conditions across multiple ranges.
• All conditions must be met (logical AND) for a cell to be counted.
• Conditions can be text, numbers, logical expressions, or cell references.
• Criteria can also include wildcards like * (any number of characters) and ? (any single character) for text-based conditions.

Use Cases:

• Counting the number of records that meet multiple criteria, such as sales above a certain amount by a specific salesperson or products within a particular price range and category.
• Analyzing data across different attributes, such as counting how many orders were placed by a particular customer during a certain time period.