how about:

SELECT ISNULL(name, '') AS [Count_no_NULLs] FROM COUNT_TEST;

that is what you are trying to do right, count the rows that are not NULL?