Sounds to me like it'd be an ideal application to use a pivot table

Without a sample workbook with sufficient data, it's hard to make an example