data matching multiple criteria to be combined

Hello, trying to figure out how to use formulas in Excel to process the following data (top rows in table) and get to the desired result (lower rows in table). Any help is much appreciated. Thank you...