How to count values if date is less than by using COUNTIF function

Using the versatile COUNTIF and COUNTIFS functions, see how you can count cells up to a certain date. How to count values if date is less than by using COUNTIF function article shows an essential tip for creating YTD (year-to-date) reports.

Counts like this rarely stay in the workbook they were written in. If yours ends up in a recurring report, the formulas can drive a reporting application instead, with access controlled per recipient.

Syntax

=COUNTIFS(date range, <minimum date)

=COUNTIF(date range, <minimum date)

Steps

1. Type =COUNTIFS(
2. Select or type range reference that includes the date values you want to apply the criteria against: $C$3:$C$10
3. Type minimum date criteria with less than operator "<1/1/2012"
4. Type ) and press Enter to complete the formula

Note: The COUNTIF function uses exact same syntax.

How

Both COUNTIF and COUNTIFS functions can be used to count values that meet a criteria. They search a given criteria in a reference  range. Ability to use criteria with logical operators like less than (<) provides the way to count values if date is less than a defined value.

We used "<1/1/2012" criteria to define maximum date and search is made on date range $C$3:$C$10.

=COUNTIFS($C$3:$C$10,"<1/1/2012")

=COUNTIF($C$3:$C$10,"<1/1/2012")

Alternatively, you can write the formula by using cell references. For example; if your maximum date value is at cell K9, then criteria would be written as “<=”&K9.

=COUNTIFS($C$3:$C$10,"<"&$K$9)

=COUNTIF($C$3:$C$10,"<"&$K$9)

Also see related articles how to count values if date is greater than by using COUNTIF function, and how to count values between two dates.