FormulaBrief

Guide 04 / conditional count

COUNTIFS with dates and text criteria

Count rows that meet a text condition and a date condition without turning dates into fragile text.

Build a Formula From My Task

BASE PATTERN

Start with one status and one date boundary

Suppose column B contains a ticket status, column C contains the closed date, and F1 contains the first date to include. Join the comparison operator to the cell that holds the date.

Closed tickets on or after the date in F1

=COUNTIFS(B2:B500,"Closed",C2:C500,">="&F1)

The criteria ranges must cover the same rows. Excel compares date serial values; it does not need a locale-sensitive date string inside the formula.

DATE RANGE

Use an exclusive upper bound for a date window

Keep the starting boundary inclusive and the ending boundary exclusive. If G1 is the first day after the reporting window, this pattern also handles timestamps in column C.

Closed tickets from F1 up to, but not including, G1

=COUNTIFS(B2:B500,"Closed",C2:C500,">="&F1,C2:C500,"<"&G1)

For a calendar month, put its first day in F1 and the first day of the next month in G1. This avoids excluding rows that contain hours and minutes on the final calendar date.

DEBUG

Check why a plausible formula returns zero

  1. 1. Confirm that column C contains real Excel dates, not text that only looks like dates.
  2. 2. Confirm that B2:B500 and C2:C500 start and end on the same rows.
  3. 3. Check spaces and spelling in the status values.
  4. 4. Test each condition separately before combining them.

SOURCE

Function reference

Microsoft documents COUNTIFS as applying criteria to multiple ranges. Review the official syntax and platform notes before adapting the examples to a production workbook.

Microsoft Support: COUNTIFS function →