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
- Type =COUNTIFS(
- Select or type range reference that includes the date values you want to apply the criteria against: $C$3:$C$10
- Type minimum date criteria with less than operator "<1/1/2012"
- 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.
