Usually. it is a bad idea to mix in a table numbers and text
Maybe you can get rid of the "A" in this table, and your formula will work, with a little modification
your formula evaluates the range , and counts how many cells are >0 (and no mattes what values you have in D9:AH9...
=SUMPRODUCT(--(D9:AH9>0);--(WEEKDAY($D$3:$AH$3)=1))
that is why, you get 4 as the result, for excel the "A" cells are >0 , and the result means that there are 4 cells in the sum range for sunday...
try instead of "A" leave the cells blank (and make your conditional formatting formula to mark the blanks with red), and remove >0 from formula, you will get the number of hours worked in sundays...
try this:
=SUMPRODUCT(--(D7:AH7);--(WEEKDAY($D$3:$AH$3)=1))