Count dates from range in given year Excel

In this article, we will learn how to Count dates from a range in a given year Excel.

In simple words, while working with date data tables, sometimes we need to count the cells where dates are from the same year. Year value be given and need to count the date values which are from the given year using a formula explained below. Criteria inside the formula executed using the operators. Operators like equals to ( = ), less than equal to ( <= ), greater than ( > ) or not equals to ( <> ).

How to solve the problem?

For this article we will be required to use the SUMPRODUCT function. Now we will make a formula out of these functions. Here we are given dates in a range data and a specific year value. We need to count the date values where the formula includes the specific given year.

Generic formula:

= SUMPRODUCT ( -- ( YEAR(range) = year ) )

range : date values given in as range
year : year value given as cell reference.
= : operator which matches the criteria

Example:
All of these might be confusing to understand. So, let's test this formula via running it on the example shown below.
Here we have the date records and we need to find the date values lying in a specific year or return the count rows where the date value lays within a given year.


Firstly, we need to define the named range for dates range as range. Just select the date values and type the name (range) for the range in the top left corner.

Now we will use the following formula to get the count of dates which lays in year 2016.
Use the Formula:

= SUMPRODUCT ( -- ( YEAR ( range ) = F4 ) )

range : named range used for date values D3:D11.

F4 : year value given as cell reference.

= : operator, condition given as equals to sign.

Explanation:

  • YEAR ( range ) function extracts the year value from the date and matches it with given year and operator.

= SUMPRODUCT ( -- ( { 2019 ; 2018 ; 2019 ; 2016 ; 2020 ; 2019 ; 2017 ; 2016 ; 2020 } = 2016 ) )

  • = operator matches it with year value 2019 and returns an array TRUE and FALSE values depending on the operation.

= SUMPRODUCT ( -- ( { FALSE ; FALSE ; FALSE ; TRUE ; FALSE ; FALSE ; FALSE ; TRUE ; FALSE } ) )

  • -- operator used to convert TRUE value to 1 & False value to 0.

= SUMPRODUCT ( { 0 ; 0 ; 0 ; 1 ; 0 ; 0 ; 0 ; 1 ; 0 } )

  • SUMPRODUCT function gets the sum of 1s in the returned array, which will be the count of required dates or rows.


Here the year value is given as cell reference and range is given as cell reference. Press Enter to get the count.

As you can see the total date matches the year 2016 comes out to be 2. It means there are 2 date values or 2 rows where the year value equals to 2016.

Now copy and paste the formula to other cells using the CTRL + D or drag down option of Excel.

As you can see in the above snapshot we obtain all the date values which lays in a specific year using the Excel formula. You can also obtain check the same using the excel filter option. Apply the filter to the Dates header and deselect the select all option and select the year option which you need to filter.

For the year 2019, Select the year 2019 and click OK.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube