How to Count Dates by Year in Excel Using a Formula
Question details
The user needs to count the number of date entries that fall into specific years within a given dataset.
- Product
- Microsoft Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Analyzing a continuous range of dates (such as D2:D100) to find out exactly how many dates occur in specific target years (like 2010, 2011, etc.).
- Observed behavior
- The goal is to correctly extract the year from the date range and return a total count for each specified year without manually filtering the data.
Ensure that your data range contains valid date formats recognized by the spreadsheet program. The formulas will return an error or an incorrect count if the dates are stored as plain text strings.
Use the SUM and YEAR Array Formula
This method uses the YEAR function to extract the year from a range of dates, compares it to your target year, and uses the SUM function to count the matches.
By combining the SUM and YEAR functions, you can create a powerful array formula. The double negative (--) is used to convert the TRUE and FALSE values returned by the condition into 1s and 0s, which can then be summed up.
In an empty column next to your data, list the years you want to count. For example, enter 2010 in cell J2 and 2011 in cell J3.
Select the cell next to your first target year (e.g., K2) and enter the following formula: =SUM(--(YEAR($D$2:$D$100)=J2)). Make sure to adjust the range $D$2:$D$100 to match your actual date data.
If you are using Microsoft 365 or Excel 2021, simply press Enter. If you are using an older version of Excel, you must confirm the formula by pressing Ctrl+Shift+Enter. Curly brackets {} will appear around the formula.
Click on cell K2, hover over the bottom-right corner until you see the fill handle (a plus sign), and drag it down to fill the formula for the remaining years.
Use the SUMPRODUCT Function as an Alternative
SUMPRODUCT natively handles arrays, meaning you do not have to worry about pressing Ctrl+Shift+Enter in older versions of your spreadsheet software.
Count Dates and Analyze Data Effortlessly with WPS Office
WPS Spreadsheet offers full support for advanced array formulas, SUMPRODUCT, and dynamic date functions, allowing you to seamlessly analyze, filter, and count data by year without any steep learning curve.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing the dates you need to count.
- 2. Input the target years: Create a summary table by typing the years you want to count into a blank column.
- 3. Enter the formula: Type =SUMPRODUCT(--(YEAR($D$2:$D$100)=J2)) next to your first year and press Enter. WPS Spreadsheet processes the array natively.
- 4. Drag to fill: Click the cell's fill handle and drag it downward to instantly calculate the totals for the remaining years.
Frequently Asked Questions
Why does my formula return a #VALUE! error?
This typically occurs if there are text strings or non-date values in your date range (D2:D100) that the YEAR function cannot process. Ensure all cells in your selected range are properly formatted as dates and do not contain text headers.
Can I use COUNTIFS to count dates by year instead of an array formula?
Yes, you can use the COUNTIFS function by specifying a start and end date for the year. For example, to count dates in 2010, you can use: =COUNTIFS(D2:D100, ">=1/1/2010", D2:D100, "<=12/31/2010"). This avoids the need for array processing entirely.
How do I handle blank cells so they aren't counted as the year 1900?
To ignore blank cells in your formula, you can add an additional condition to check for blanks. Modify your formula to: =SUMPRODUCT(--(YEAR($D$2:$D$100)=J2), --($D$2:$D$100<>"")). This ensures empty cells are excluded from your count.
