SUMIFS function will be used to demonstrate how to sum if by year.
Sum if by year
Considering the example below, the formula in F5 is: =
Figure 1 – Sum if by year
Extending the formula through column F, you will get the result like this
Figure 2 – Sum if by year
The syntax of SUMIFS function is:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
In this case, sum_range is C3:C9, and criteria_range (or date_range) is B3:B9. There are 2 criteria happen at the same time in the formula.
You will need to understand how they work.
- First criterion: B3:B9,”>=”&DATE(E5,1,1). Cells in range B3:B9 that >= 01/01/2015
- Second criterion: B3:B9,”<=”&DATE(E5,12,31). Cells in range B3:B9 that <= 12/31/2015 will be added to sum.
Remember to press F4 after the sum_ranges and date_ranges as you will not want to change the ranges when you extend the formula down column F.