Hi - Dave here.
In the past few months, I've shared formulas to sum values by week, by month, and by quarter. This week, I want to round things off with a formula to sum values by year.
Many people will try to solve this with the SUMIFS function, which does work, albeit with a slightly complicated formula. However, SUMPRODUCT is easier.
In the worksheet below, we are using a formula to calculate total amounts for three years: 2020, 2021, and 2022. The formula in cell G5 is:
=SUMPRODUCT((YEAR(data[Date])=F5)*data[Amount])
[
download the workbook and read the full explanation]
As I mentioned, this is a case where SUMIFS is more difficult than SUMPRODUCT. However, the article above explains how to solve the problem with both functions. I've also included an all-in-one dynamic array formula that builds the entire summary table with the new BYROWS function. This is the future of Excel formulas. Download the workbook and try it out yourself.
Excel formulas
We maintain a large list of working formulas
here.
If you need more structure, we also offer
video training.
Have a fun and safe weekend!
Dave
