ExcelExplained

Sum sales by year using SUMIFS

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

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 range "dates" (B7:B106) to check if the transaction took place between January 1st and December 31st on the year.

The two conditions 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 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.

If you have additional questions please let me know by posting a comment.

Add comment

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