[SOLVED] Count number of empty cells and sum up too the first non-empty cell.

Here's something else for you to consider (I implemented everything through row 100):

Data Validation
Highlight B2:B100 > Data > Data Validation > Allow: List > Source: =Ingredients!$A$2:$A$30 > OK

Conditional Formatting
Highlight A2:AI100 > Conditional Formatting > New Rule > Use a formula
=$B2="Total"
Format: Grey fill with bold font

Formulas
D2 =IFERROR(E2*4+F2*9+G2*4,"")
E2 =IFERROR(IF($B2="Total",SUM(E1:E$2)-SUMIF($B1:$B$2,"Total",E1:E$2)*2,VLOOKUP($B2,Ingredients!$A$1:$AG$29,COLUMN(A$1)+2,FALSE)*$C2),"")

Drag the formula in E2 through AI2 and then highlight D2:AI2 and drag down through row 100.

See attached workbook for clarification.