A quick and easy way to count between dates is by using the COUNTIFS formula. Create formula-based filters, find your sales figures, or organize your tables in no time.
Syntax
=COUNTIFS(date range, >=minimum date, date range, <=minimum range)
Steps
- Type =COUNTIFS(
- Select or type range reference that includes date values you want to apply the criteria against $C$3:$C$10
- Type minimum date criteria with equal or greater than operator ">=1/1/2012"
- Add the date range again $C$3:$C$10
- Type maximum date criteria with equal or less than operator "<=12/31/2012"
- Type ) and press Enter to complete formula
How
COUNTIFS function counts values that meet single or multiple criteria. Ability to use criteria with logical operators like greater than or equal (>=) and less than or equal (<=) provides the way of counting values between values.
To filter dates in a year, we need two criteria and suitable operators. We used ">=1/1/2012" and "<=12/31/2012" criteria to define start and end dates of searching. Search is made on same date range $C$3:$C$10.
=COUNTIFS($C$3:$C$10,">=1/1/2012",$C$3:$C$10,"<=12/31/2012")
Tip: Order of dates does NOT matter. "<=12/31/2012" criteria can be placed as first criteria.
Alternative way to write this formula is using cell references instead of static dates. For example; if your minimum date value is at cell K9, then criteria would be written as “>=”&K9. To make formula more dynamic; use DATE function to create dates instead of writing all date text.
=COUNTIFS($C$3:$C$10,">="&DATE($K$9,1,1),$C$3:$C$10,"<="&DATE($K$9,12,31))
Note that we wouldn't be able to handle this scenario of count between dates with COUNTIF formula because it can only handle single criteria like greater than or less than but not both at the same time.
Also see the article on how to sum between two dates using SUMIFS formula.
