Count cells that are dates and in a particular year

You can display the number of cells that contain a date in a particular year by using the COUNTIFS function.
This function uses two criteria to satisfy two conditions.


Example

Lets assume the years have been entered into a column.
The first step is to create a date which is the first day of that year.
The second step is to create a date which is the last day of that year.

 AB
1=DATE(B1, 1, 1) = 01 Jan 20202020
2=DATE(B1, 12, 31) = 31 Dec 2020 
3=DATE(B3, 1, 1) = 01 Jan 20212021
4=DATE(B3, 12, 31) = 31 Dec 2021 
5=DATE(B5, 1, 1) = 01 Jan 20222022
6=DATE(B5, 12, 31) = 31 Dec 2022 

Once we have the start and end dates we can use these dates to create our criteria.
The first criteria is any date greater than or equal to the start date.
The second criteria is any date less than or equal to the end date.

 ABCD
1=COUNTIFS(D1:D8, ">="&B1, D1:D8, "<="&B2) = 201 Jan 2020202001 Jul 2020
2=COUNTIFS(D1:D8, ">="&B3, D1:D8, "<="&B4) = 431 Dec 2020 24 Oct 2021
3=COUNTIFS(D1:D8, ">="&B5, D1:D8, "<="&B6) = 201 Jan 2021202103 Mar 2021
4 31 Dec 2021 02 Dec 2022
5 01 Jan 2022202211 Dec 2020
6 31 Dec 2022 24 Feb 2021
7   25 Feb 2022
8   16 Apr 2021

Alternative Formulas

The COUNTIFS function was added in Excel 2007.
Before Excel 2007 there were two ways to perform conditional counting.
One was using the SUMPRODUCT function and the other was using Array Formulas.
If you are using the SUMPRODUCT function you will also need to use the double minus operator to convert boolean values into there corresponding ones and zeros.


Built-in Functions

COUNTIFS - The number of numerical values that satisfies multiple conditions.
DATE - The date as a date serial number given a year, month, day.


Related Formulas

Count cells that are numbers in a range
Count cells that are dates between 2 dates


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top