SUMIF has no year criteria, so you either extract the year first or test it inside the formula. The helper-column route: add a column with =YEAR(A2), fill it down, then use =SUMIF(C2:C16,2021,B2:B16). The one-formula route needs no helper column: =SUMPRODUCT(--(YEAR(A2:A16)=2021),B2:B16).
The YEAR comparison produces an array of TRUE and FALSE, the double minus turns those into 1s and 0s, and multiplying by the values column leaves only the 2021 rows to add up. The third option is SUMIFS with two date bounds, which avoids arrays entirely: =SUMIFS(B2:B16,A2:A16,">="&DATE(2021,1,1),A2:A16,"<="&DATE(2021,12,31)) — the fastest of the three on large data.
Whichever you use, point the year at a cell rather than hard-coding it so you can drag the formula down a list of years.
To sum values based on a specific year in Excel, we have two methods to follow. The first method uses a helper column where you need to add a column with the year number and sum values based on the year from that column.
The second method uses a different approach. It creates an array by extracting years from the date column and then sums values based on a year from that array.
In this tutorial, we will learn both methods to sum values based on a year.
SUMIF Year with a Helper Column
- First, add a new column and enter the YEAR function to get the year number from the dates.
- Now, go to the next column and enter the SUMIF function there.
- In the SUMIF function, refer to the year column for the range argument.
- Next, in the criteria argument, enter the year that you want to use to sum values.
- In the end, refer to the quantity column for the sum_range, enter the closing parenthesis, and hit Enter to get the result.
Once you hit Enter, you get the sum of values for the year 2021, as you have specified in the criteria.
=SUMIF(C1:C16,2021,B1:B16)
Use SUMPRODUCT to Sum Values Based on a Year
Creating a formula by using SUMPRODUCT is way more powerful. With this formula, you don’t need to add a helper column.
- First, enter the SUMPRODUCT function and enter the double minus sign “–“.
- After that, enter the YEAR function and refer to the entire range where you have dates.
- Next, use an equals sign and enter the year value you want to use for the sum.
- From here, enclose the YEAR condition within parentheses.
- Now, in the second argument of SUMPRODUCT, refer to the range where you have the values that you want to sum.
- In the end, hit Enter to get the result.
=SUMPRODUCT(--(YEAR(A2:A16)=2021),B2:B16)
To understand this formula, you need to break it into three parts.
In the first part, you have the YEAR function with a condition to check for the year 2021 in the dates. This condition returns an array of TRUE and FALSE according to the year that you have in each date. If a date has the year 2021, it returns TRUE; otherwise, FALSE.
In the second part, you have two minus signs (–) which convert TRUE and FALSE values into 1 and 0, respectively.
In the third part, you have the two arrays, one with the values 1 (for the year 2021) and 0 (for the years other than 2021), and the second array with the values from the sum range. SUMPRODUCT multiplies both arrays with each other and then sums those values.
Now here’s one thing that you need to understand: When you multiply zero with any values, it returns 0. So, you are only left with the values where you have 1 in the first array.
To make the year dynamic, type it in a cell, like E1, and refer to that cell instead of typing 2021:
=SUMPRODUCT(--(YEAR(A2:A16)=E1),B2:B16)
Just make sure the date column has only dates or blank cells. If a cell in the range has text, YEAR returns #VALUE!, and so does the whole formula.
SUMIFS with a Date Range for a Year
There’s a third method that needs neither a helper column nor an array. With SUMIFS, you can sum the values where the date is on or after the 1st of January and before the 1st of January of the next year. Here’s the formula with the year in cell E1:
=SUMIFS(B2:B16,A2:A16,">="&DATE(E1,1,1),A2:A16,"<"&DATE(E1+1,1,1))
Using “less than the 1st of January of the next year” instead of “less than or equal to the 31st of December” also includes dates on 31st December that have a time. SUMIFS is also faster than SUMPRODUCT on large data, and it ignores text in the date column instead of returning an error.
Sum Values by Year and Month
You can add one more condition to the SUMPRODUCT formula to sum the values for a specific month of a year. The following formula sums the values for June 2021:
=SUMPRODUCT((YEAR(A2:A16)=2021)*(MONTH(A2:A16)=6),B2:B16)
And if you need the totals for every year at once, a pivot table is the quickest way: add the date column to Rows, group it by Years, and add the quantity to Values.
Frequently Asked Questions
Why can’t I use YEAR inside SUMIF?
The range argument of SUMIF must be a range of cells, not a calculated array, so SUMIF(YEAR(A2:A16),2021,B2:B16) doesn’t work. Use a helper column, SUMPRODUCT or the SUMIFS date range method instead.
Why does my formula return 0?
Most often, the dates are stored as text, so they don’t match the year. Check with =ISNUMBER(A2); if it returns FALSE, convert the dates to real dates first.
Can I sum by financial year?
Yes. Use the SUMIFS date range method with the start and end dates of your financial year, like DATE(2021,4,1) and DATE(2022,4,1) for a year that starts in April.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.