Formula to combine Hlookup with SumProduct

Here is my problem; I have been using the. attached spreadsheet for the past several months. As of late the company has changed the sheet to allow Coaching Recipient comments (row 13. The problem is that not all of the data sheets have this row and it causes an issue with my formula in ap15, which is a SumProduct function. What I am trying to do is find a way to use an HLookup along with the SumProduct function to get the value I need. Ant suggestions would be appreciated.

Thank you in advance

AttachmentSize
CLEAN TOTAL LOSS REI WORKBOOK 2012 - Revised.xls366.5 KB
Nick's picture

its not clear what you want

its not clear what you want to achieve here..

is it that adding the additional row has messed up the input range into the formula ?

... this sheet needs a complete overhaul
Suggest the following:
1. restructure the data so that it's stored in rows, with headings along the top.
2. This will enable you to Create pivot tables of the data for reporting purposes.

you will get in a terrible mess if you continue
Good luck

Nick