Before you start looking for a solution using macros (VBA)

Discover the almighty SUMPRODUCT Function in Excel

If you  already use SUMPRODUCT in most of your workbooks
If you use SUMPRODUCT in your powerful automated reporting spreadsheets
Then you can start discovering

VBA for Excel (Macros)

Excel Functions and Formulas Sitemap

Downloadable Tutorials, Examples and Templates