How to calculate a value out of a table without a value

What I see the current formula in I6 doing (follow along with the evaluate formula tool, if desired):

1) Look at the value in C6. Is it less than 1? In this case, C6 is 0, so this test returns TRUE, so the IF() function will proceed to the value_if_true argument.
2) The value if true argument is a space character.
3) Take the sum of C6:D6 (4).
4) Multiply 4 by a space character. Excel does not know how to multiply a number by text, so it generates and error.
5) Divide the error value by 1000, which propagates the error.

The solution is to replace that space character with a number or to otherwise change the flow of logic so that you are not asking excel to multiply numbers and text. I'm not sure I understand what you want to happen when C6 is less than 1, so I cannot recommend something specific.

Do you mean = IF($C6<1,"",VLOOKUP($A6,Sheet1!$A$15:$E$15,3,0)*$C$4*SUM($C6:$G6)/1000)

It's not clear to me what I6 should be when C6 is 0. The formula in I11 is a simple sum of I6:I10, so maybe I6 should be 0 when C6 is 0? Which would be a simple edit to replace the space character with the number 0?