COUNTA probs / dynamic formulas / complex(ish) SUM formula

Hey peeps a few quick questions that the text books don’t seem to have the answer to!

(Have included a basic example of my workbook attached)

I have a table which has some formulas in it, every week I will paste new data under it, sometimes there will more rows, sometimes less.

• I have the formula =COUNTA(DATA) but when I paste data in it includes the blank fields as data. I have to individually select the ‘blank’ cells and press delete for it to recognize these as blanks. I have tried ‘pasting special’ as ‘values’ and have checked leave blank cells but it still considers them values

• Is it possible to have a SUM formula that only counts instances where there is a letter in another column? (in the example the formula should return 70)

• Lastly when I paste the data in for the ‘Sum of Value’ formula I have to change the data range manually, can this formula be set up to automatically select the data range that is pasted in?

Hopefully I made sense! Any help is, as always, greatly appreciated

Adele x