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.
|
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.
|
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
<A HREF="../functions/countifs-function.htm"><U>COUNTIFS</U></A> - The number of numerical values that satisfies multiple conditions.
<A HREF="../functions/date-function.htm"><U>DATE</U></A> - 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