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))
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.
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
"<=" need to be entered as text between double quotes, you will have to use the symbol
& to concatenate the operator with each date.
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.
My name is Radu Meghes, and I'm the owner of excelexplained.com. Over the past 15+ years, I have been using Microsoft Excel in my day-to-day job. I’ve worked as an investment and business analyst, and Excel has always been my most powerful weapon. Its flexibility and complexity make it a highly demanded skill for finance employees. I launched excelexplained.com back in 2017, and it has become a trusted source for Excel tutorials for hundreds of thousands of people each year.
If you'd like to get in touch, you can contact me on LinkedIn.
Thousands of people have benefited from my free Excel lessons. My only question is, will you be next?
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.