You can use the following formula to count the number of occurrences by year in an Excel spreadsheet:
=SUMPRODUCT(1*(YEAR(A1:A10)=2020))
This particular formula counts the number of dates in the range A1:A10 that occur in the year 2020.
The following example shows how to use this formula in practice.
Example: Count by Year in Excel
Suppose we have the following dataset that shows the sales of some product on various dates:
Now suppose we’d like to count the number of dates by year.
To generate a list of unique years, we can use the following formula:
=SORT(UNIQUE(YEAR(A2:A15)))
We’ll type this formula into cell D2:
Next, we can use the following formula to count the number of dates by year:
=SUMPRODUCT(1*(YEAR($A$2:$A$15)=D2))
We’ll type this formula into cell E2, then copy and paste it into each remaining cell in column E:
From the output we can see:
- The year 2018 occurred 2 times.
- The year 2019 occurred 3 times.
- The year 2020 occurred 2 times.
And so on.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Count by Month in Excel
How to Use COUNTIF with OR in Excel
How to Count If Cells Contain Text in Excel
How to Count Unique Values Based on Multiple Criteria in Excel