How to Sum by Year in Excel

To sum by year in Excel, add up the amounts whose dates fall between 1 January of that year and 1 January of the next. SUMIFS does this with two date conditions, and it works in every current version of Excel. This guide shows that formula step by step, two shorter alternatives, and how to fix a total that comes back as 0. It is for anyone building yearly reports, budgets or a summary in a job test.

The sample data#

All the examples use this sales list in A1:C8. The dates are real Excel dates, not text.

The goal is a small summary: one total per year.

How to sum by year with SUMIFS#

SUMIFS adds the numbers in one range when other ranges meet all your conditions. To sum one year, check the date column twice. Type the year as a plain number, 2025, in E2, then in F2:

=SUMIFS(C2:C8,A2:A8,">="&DATE(E2,1,1),A2:A8,"<"&DATE(E2+1,1,1))

The result is 3,880 (850 + 1,430 + 990 + 610). Here is what each part does:

• C2:C8 is the range to add, the amounts.
• A2:A8, ">="&DATE(E2,1,1) keeps dates on or after 1 January 2025.
• A2:A8, "<"&DATE(E2+1,1,1) keeps dates before 1 January 2026.

DATE(year, month, day) builds a real date from three numbers, so DATE(2025,1,1) is 1 January 2025. The & joins the operator in quotes to that date. Writing ">=DATE(E2,1,1)" all inside the quotes would not work, because Excel would treat it as text. The dates in criteria lesson practises exactly this, and the SUMIFS function guide covers every argument.

Why "before next 1 January" and not "on or before 31 December"#

Dates can carry a time. A sale at 3 pm on 31 December 2025 is stored as 31 December plus a fraction of a day, which is greater than DATE(2025,12,31). A condition of "<="&DATE(E2,12,31) would miss it. "<"&DATE(E2+1,1,1) catches every moment of the last day.

How to fill the formula down for every year#

Put the years in a column, lock the data ranges, and fill down. Type 2024, 2025 and 2026 in E2:E4, then in F2:

=SUMIFS($C$2:$C$8,$A$2:$A$8,">="&DATE(E2,1,1),$A$2:$A$8,"<"&DATE(E2+1,1,1))

Select F2:F4 and press Ctrl+D (⌘D on a Mac):

The $ signs keep the data ranges fixed, while E2 changes to E3 and E4. The three totals add up to 6,980, the sum of the whole list, which is a quick check that no row was missed or counted twice.

How to sum by year with SUMPRODUCT and YEAR#

SUMPRODUCT can test the year directly, which gives a shorter formula:

=SUMPRODUCT((YEAR(A2:A8)=E2)*C2:C8)

YEAR returns the year of each date. (YEAR(A2:A8)=E2) gives TRUE or FALSE for each row, and multiplying by the amounts keeps only the 2025 rows. The result is again 3,880.

SUMPRODUCT works in every version and needs no special keys. On very large lists it can be slower than SUMIFS, and it returns #VALUE! if any cell in the date range holds text. You can't use YEAR inside SUMIFS's criteria range: =SUMIFS(C2:C8,YEAR(A2:A8),2025) is not allowed, because SUMIFS needs a real range.

How to sum by year with a helper column#

A helper column makes the formula simple and the year visible. In D2, type =YEAR(A2) and fill down. Then:

=SUMIF(D2:D8,2025,C2:C8)

The result is 3,880. This is a good choice when other people will read the sheet, or when you also want to filter or sort by year.

How to sum by year with a second condition#

SUMIFS takes more pairs, so add the region after the dates. North sales in 2025:

=SUMIFS(C2:C8,A2:A8,">="&DATE(2025,1,1),A2:A8,"<"&DATE(2026,1,1),B2:B8,"North")

The result is 2,040 (1,430 + 610). The COUNTIFS and SUMIFS lesson builds multi-condition totals like this one step at a time.

The same date pattern works for any period. Change the boundaries to get a sum by month, a sum by week or a sum between any two dates. For a running total from 1 January to today, see the year-to-date total. If your year starts in April or July, work out the fiscal year from a date in a helper column first.

How to sum by year with a PivotTable#

A PivotTable needs no formulas at all:

1. Click anywhere in the data and choose Insert > PivotTable, then OK.
2. Drag Date to Rows and Amount to Values.
3. If Excel doesn't group the dates by itself, right-click any date in the pivot, choose Group, select Years and click OK.

The pivot shows 1,200 for 2024, 3,880 for 2025 and 1,900 for 2026. Click Refresh after the data changes.

Why your sum by year returns 0 or an error#

A total of 0 means no row passed the conditions, and an error means one part of the formula failed. Check these in order:

1. Dates stored as text. Real dates align right by default. Text dates align left and never match. Convert them with DATEVALUE or Data > Text to Columns.
2. The year cell holds a date. If E2 contains 1 January 2025 instead of the number 2025, DATE(E2,1,1) gets a year above 9999 and the formula returns #NUM!. Set E2 to General and type 2025.
3. Operators inside the date. Write ">="&DATE(E2,1,1), not ">=DATE(E2,1,1)".
4. Ranges of different sizes. SUMIFS returns #VALUE! if C2:C8 and A2:A9 don't match. Make every range cover the same rows.

Practise it#

Practise dates in criteria in a live spreadsheet: you type the formula and it checks it instantly. Then try the exercise on this page, which totals the 2025 sales with SUMIFS and DATE.