Sum sales by year using SUMIFS

December 4, 2018

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.

Yearly sales using SUMIFS

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 view another example of how to 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.

Thousands of people have benefited from my free Excel lessons. My only question is, will you be next?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 comments on “Sum sales by year using SUMIFS”

Essential Functions

Excel Topics

Excel courses

I have compiled a list of the best Excel online courses. It covers basic and advanced topics, VBA, and Power BI.
View courses
Excel is a very complex application, and understanding it on your own may prove difficult and time-consuming. I have created ExcelExplained.com to help you better understand each function and provide useful examples for different tasks. If you can’t find the answer to your question in one of the published articles, please leave a comment or send me an email, and I will do my best to help you out.

Affiliate Disclosure: When you purchase a product through a link on our website, I may earn an affiliate commission. There are no additional costs to you.
Copyright © 2017-2021 ExcelExplained.com