# How to sum by year in Excel

Use SUMIFS with January 1 and December 31 of the year in E2: `=SUMIFS($C$2:$C$10,$A$2:$A$10,">="&DATE(E2,1,1),$A$2:$A$10,"<="&DATE(E2,12,31))`.

**Excel:** `=SUMIFS($C$2:$C$10,$A$2:$A$10,">="&DATE(E2,1,1),$A$2:$A$10,"<="&DATE(E2,12,31))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Date | Category | Amount |  | Year | Total |
| 2024-03-14 | Software | 1200 |  | 2024 |  |
| 2024-11-02 | Hardware | 3450 |  | 2025 |  |
| 2025-01-20 | Software | 1320 |  | 2026 |  |
| 2025-06-08 | Travel | 890 |  |  |  |
| 2025-09-30 | Hardware | 2100 |  |  |  |
| 2025-12-31 | Software | 1450 |  |  |  |
| 2026-01-01 | Travel | 640 |  |  |  |
| 2026-02-17 | Software | 1580 |  |  |  |
| 2026-04-05 | Hardware | 2875 |  |  |  |

Formula in F2, filled to F4: `=SUMIFS($C$2:$C$10,$A$2:$A$10,">="&DATE(E2,1,1),$A$2:$A$10,"<="&DATE(E2,12,31))`

Result: $4,650; $5,760; $5,095

## How it works

DATE turns the year number into its first and last day, and SUMIFS adds the amounts dated between them, both days included. December 31 and January 1 fall in the right years. The locked `$` ranges stay put while `E2` moves down the list of years.

## Other ways

- `=SUMPRODUCT((YEAR($A$2:$A$10)=E2)*$C$2:$C$10)`: Shorter: YEAR pulls the year from every date and the test keeps the matching rows.

## Common mistakes

- `=SUMIF(A2:A10,2025,C2:C10)` compares each date with the number 2025, a date in 1905, and returns 0. Build a date range or use YEAR.
- The year in E2 must be a number. A year typed as text, or a date formatted to show only the year, gives 0 or the wrong total.

## Questions

**How do I sum by fiscal year?**

Shift the dates before taking the year. For a year starting July 1: `=SUMPRODUCT((YEAR(EDATE($A$2:$A$10,6))=E2)*$C$2:$C$10)`.

**How do I sum by year and category?**

Add a pair to SUMIFS: `…,$B$2:$B$10,"Software")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sum-by-year