Sumproduct using blank cells

Looking at your posted workbook....
I believe the problem is in cell AU9
Try this:

AU9: =SUMPRODUCT((0&F9:AT9)*(F12:AT12<>""))*100
or...an alternative structure:
AU9: =SUMPRODUCT(F9:AT9,--(F12:AT12<>""))*100

Does that help?

I think I understand....

IF...The Cumulative Weight Pct can be different for each student (based on
whether they have a score for a particular item), then try this:

Raw Score
B12: =IF(COUNT(F12:AT12),ROUND(SUMPRODUCT($F$9:$AT$9,F12:AT12),2),"")

Grade%
C12: =IF(B12="","",B12/SUMIF(F12:AT12,"<>"&"",$F$9:$AT$9)/100)

Ltr Grade
D12: =IF(B12<>"",HLOOKUP(B12/SUMIF(F12:AT12,"<>"&"",$F$9:$AT$9)/100,
GradeTable,2),"")

GPA
E12: =IF(B12<>"",HLOOKUP(B12/SUMIF(F12:AT12,"<>"&"",$F$9:$AT$9)/100,
GradeTable,3),"")

Copy those formulas down for each student.

HOWEVER...if the Cumulative Weight Pct is static for ALL students and
each pct is only used when ANY student has a score for that category,
then: I'd put Effective Weight Pct formulas under each Weight Pct to
determine if it's used or not:

F10: =F9*(COUNTIF(F12:F13,"<>"&"")>0)
Copy that formula across.
AU10: =SUM(F10:AT10)*100

Then you can use your original formulas, but reference $AU$10, instead of $AU$9.

Does that help?