Select Page

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.

## 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.