Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to sum values by year in Excel and Google Sheets
Topic:SUM & aggregationExcel & Google Sheets
=SUMPRODUCT((YEAR(A2:A5)=2024)*B2:B5)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Ensure column A holds dates and column B holds the numeric values to sum.
- 2In an empty cell type =SUMPRODUCT((YEAR(A2:A5)=2024)*B2:B5), replacing 2024 with the target year.
- 3Press Enter. Rows where YEAR(A) equals 2024 contribute their B value; other rows contribute 0.
Tips
- Replace the hard-coded year with a cell reference (e.g. D1) to build a dynamic year summary table.
- An alternative with SUMIFS: =SUMIFS(B2:B5,A2:A5,">="&DATE(2024,1,1),A2:A5,"<="&DATE(2024,12,31)) — this avoids the YEAR helper but is wordier.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Why does SUMPRODUCT give me 0 when my dates look right?
Dates stored as text won't be recognised by YEAR(). Convert them to real dates using DATEVALUE() or by reformatting the column.
Can I sum by fiscal year instead of calendar year?
Yes — define your fiscal year start month and adjust: e.g. for a July fiscal year use =SUMPRODUCT((YEAR(A2:A5+(31*5))=2024)*B2:B5) to shift months.
More on SUM & aggregation
See all →Reference
SUMPRODUCT
Multiply ranges together row by row and sum the result — ideal for weighted totals.
How-toHow to sum a column
Add up an entire column with SUM. Total a whole column or a fixed range — identical in Excel and Google Sheets.
Error fixFix 0
SUM returns 0 or the wrong total in Excel and Google Sheets — fix numbers stored as text, filtered ranges, manual calculation mode, and circular references.
ReferenceSUBTOTAL
Aggregate a range (sum, average, count…) while ignoring other SUBTOTALs and, optionally, hidden rows. Ideal under filters.
How-toHow to create a running total
Build a cumulative running total with a SUM and a clever anchored reference that grows as you copy it down. Works in both apps.
How-toHow to sum values by month
Total amounts that fall in a given month with SUMIFS and two date bounds. Works the same in Excel and Google Sheets.