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