You can use the following formula to count the number of occurrences by year in an Excel spreadsheet:
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:
We’ll type this formula into cell D2:
Next, we can use the following formula to count the number of dates by year:
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.
The following tutorials explain how to perform other common tasks in Excel: