Hi. The file I uploaded contains four separate approaches to solving your problem in Excel. The
array formula is something I use as a matter of routine but it is not commonly used. In time, it will be largely replaced by the new
dynamic arrays of the second solution but, for the moment, that functionality is on beta test.
The
pivot table is the technique of choice for users that live by data analysis (the ones that say 'the solution is a pivot table; what was your problem again'
). That, in turn, may be overtaken by
Power Query and Power Pivot that build off recent database technologies. These are the third and fourth solutions.
At present, the only point I was trying to make is that far more exists within Excel than the usual diet of relative cell references and 'copy-down'. Some of the functionality is very good and links the solution back to the problem formulation rather than existing in its own little world of direct single-cell references A1
etc with its voodoo mysteries of anchoring ($A1
versus A$1
)
The Excel help pages should assist you.