Sum sales by year using SUMIFS

March 4, 2022October 24, 2022

If you are looking to create a breakdown of yearly sales based on a list of transactions, you can use the SUMIFS function. The formula works by defining two criteria for each year: the starting date and the end date of each year. Then, it sums all the transactions that took place during this interval.

In our example cell D2 contains the following formula:
=SUMIFS(amount ,dates, ">="&DATE(C2,1,1), dates, "<="&DATE(C2,12,31))
where:
amount: range (D7:D106)
dates: range (B7:B106)

If you prefer using cell references, this can be written as:
=SUMIFS($D$7:$D$106, $B$7:$B$106, ">="&DATE(C2,1,1), $B$7:$B$106, "<="&DATE(C2,12,31))

The result is the total amount of sales that took place in 2017. If you drag down the formula it will calculate the yearly sales for 2018 and 2019.

How this formula works to sum by date

The first argument we need to provide is the range that contains the values we want to sum, also called “sum_range”. Then, we have to provide the conditions in pairs.

In this example, the sum_range is called "amount"(D7:D106) and the two condition pairs use the Excel range "dates" (B7:B106) to check if the transaction took place between the two dates composing our interval: January 1st and December 31st of the selected year.

The two conditions needed for our Excel SUMIFS between dates formula are created using the DATE function:
">="&DATE(C2,1,1) – checks if the date of the transaction is higher than or equal to 01/01/2017 (year is defined by cell C2)
"<="&DATE(C2,12,31) – checks if the date of the transaction is lower than or equal to 12/31/2017 (year is defined by cell C2)

Please note that since the Excel operators ">=" and "<=" need to be entered as text between double quotes, you will have to use the symbol & to concatenate the operator with each date.

What to do next?

Whenever possible, you should try to improve your Excel understanding. You can also learn how to sum values by month or create a subtotal by item type using SUMIF.

If you have additional questions on how to use SUMIFS between dates or if you need Excel help related to SUMIFS, please let me know by posting a comment.