COUNTIFS multiple criteria

Intermediate

In operations reporting, you often need counts that match more than one condition (for example: a status and a date window). COUNTIFS is built for this.

COUNTIFS counts rows where all criteria are true.

The pattern looks like:

=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2)

For date windows, you typically use two criteria against the same date range:

=COUNTIFS(date_range, ">="&start_date, date_range, "<="&end_date)

Your task:

  1. Review the orders table in A1:E13.
  2. Use the report controls on the right:
    • As of date: G2
    • Start of month: G3
    • End of month: G4
    • Status filter: G5
  3. In cell G6, use COUNTIFS to count orders where:
    • Status is Shipped (matches G5)
    • Order date falls between G3 and G4 (inclusive)

Use ranges that cover only the order rows (row 2 through row 13) so your result updates if the report controls change.

Need some help?

Hint 1

You need one criteria pair for Status and two criteria pairs for Order date (>= start of month, <= end of month).

Hint 2

For date comparisons in COUNTIFS, build the criteria as text like `">="&G3` and `"<="&G4`.

Hint 3

Double-check your ranges: the Status range and Order date range should cover the same rows (2–13).

Related function(s)
COUNTIFSCOUNTIFSUMIFSAVERAGEIFS
More exercises
AVERAGEIFS multiple criteriaIntermediate
Dynamic date range SUMIFSIntermediate
SUMIFS multiple criteriaIntermediate
Count cells containing textIntermediate
Back to exercises overview

Exercise