Sum If by Year- Excel & Google Sheets

This tutorial will demonstrate how to use the SUMIFS Function to sum by year in Excel and Google Sheets.

Sum if by Year

The SUMIFS Function sums data that meets certain criteria. This example sums the Number of Sales per Year.

=SUMIFS(C3:C9,B3:B9,">="&DATE(E3,1,1),B3:B9,"<="&DATE(E3,12,31))

Here we use the DATE Function to define dates within the SUMIFS Function, using the Year column to define the year and entering in 1/1 and 12/31 for the first and last days of the year.

The operators in the SUMIFS Function are placed in double quotes (“”), and they’re joined to the date with an ampersand (&) like so:

• “>=”&DATE(E3,1,1)
• “<=”&DATE(E3,12,31).

Locking Cell References

To make our formulas easier to read, we’ve shown the formulas without locked cell references:

=SUMIFS(C3:C9,B3:B9,">="&DATE(E3,1,1),B3:B9,"<="&DATE(E3,12,31))

But these formulas will not work properly when copy-pasted elsewhere within your Excel file. Instead, you should use locked cell references like this:

=SUMIFS($C$3:$C$9,$B$3:$B$9,">="&DATE(E3,1,1),$B$3:$B$9,"<="&DATE(E3,12,31))

Read our article on Locking Cell References to learn more.

Sum Data by Year in Google Sheets

These formulas work exactly the same in Google Sheets as in Excel.