The simplest way to solve this problem is with theSUMPRODUCT functionusing the formula seen in the worksheet above.
The problem can also be solved with theSUMIFS function, or with adynamic array formula.
All three approaches are explained below.
The zeros in the first array effectively “cancel out” amounts in other years.
To read more about this topic, seeWhy SUMPRODUCT?
This limitation isdiscussed in more detail here.
For the end date, we hardcode 12 formonthand 31 fordayto create a December 31 date.
For both dates,yearcomes from cell F5, which contains 2020.
The number 44196 is the serial number for December 31, 2020.
BYROW runs through theuyearsvalues row by row.
Inside theSUM function, this value is compared toyears.
Sinceyearscontains all 12 years, the result is an array with 12 TRUE and FALSE results.
The 0s in the first array effectively “cancel out” amounts not associated with the year of interest.
TheHSTACK functioncombinesuyearsandsumshorizontally, and theVSTACK functioncombines the header row and the data to make the final table.
LET Function
The Excel LET function lets you define named variables in a formula.
BYROW can apply stock functions like SUM, COUNT, and AVERAGE or a custom LAMBDA function.
