This tutorial shows how to Sum if by year in Excel using the example below;
Formula
=SUMIFS(sum_range,date,">="&DATE(year),date,"<="&DATE(year,12,31))
Explanation
If you need to sum if by year, you can use the SUMIFS function with two criteria.
In the example shown, the formula in H6 is:
=SUMIFS(amount,date,">="&DATE(G6,1,1),date,"<="&DATE(G6,12,31))
The result is a total of amounts for 2011. When copied down, the formula also creates a total for 2012 and 2013.
How this formula works
The first argument for SUMIFs is always the range to sum (“sum_range”), and criteria are supplied as one or more range / criteria pairs.
In this example, the sum range is a named range called “amount” (E3:E2931), and the criteria are supplied in two pairs, both using a named range called “date” (B3:B2931).
In each case, the DATE function is used in the criteria to build two valid dates, both using with the same year:
1. The first day of 2011
2. The last day of 2011
These dates appear in the formula as follows:
date, ">="&DATE(G6,1,1) // date is >= to 1/1/2011 date, "<="&DATE(G6,12,31) // date is <= 12/31/2011
As a result, the formula returns a total sum for all amounts in the year 2011 only.
Because we are using a cell reference to supply the year, the formula can be copied down to create totals for 2012 and 2013 as well.