Sum if by year

SUMIFS function will be used to demonstrate how to sum if by year.  

Sum if by year

Generic Formula

=SUMIFS(sum_range,date_range,”>=”&DATE(year,1,1),date_range,”<=”&DATE(year,12,31)

Example:

Considering the example below, the formula in F5 is:  = =SUMIFS($C$3:$C$9,$B$3:$B$9,">="&DATE(E5,1,1),$B$3:$B$9,"<="&DATE(E5,12,31))

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

Explanation

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

and

  • Second criterion: B3:B9,”<=”&DATE(E5,12,31). Cells in range B3:B9 that <= 12/31/2015 will be added to sum.

Note:

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.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar