SUMPRODUCT Problem with blank non blank cells included in range and ever changing range

Hi

I have two queries with a SUMPRODUCT formula I'm trying to write:

1st - to calculate a range but I keep running into trouble due to cells that appear to be blank but are not (demonstrated in the example in cell A7 with a ') and returns a #VALUE. - I have tried a number of things ISBLANK, COUNTA, COUNTBLANK with no joy. SUMPRODUCT formula in Cell D1.

2nd - this range will vary in column length each time and i need it a simply addition to the SUMPRODUCT formula to take account of this and go down to the last cell with an actual number in column A. Hope i have explained this properly.

Obviously, report churns out the date in column A as text and it is usually more than the 7 lines i have entered.

Any help would be great.
Thanks in advance.