# How to Count by Year in Excel

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.