SUMPRODUCT SUBSTITUTE with Blank Cells

Need help with a formula. I have a column of cells that their values are chosen from a drop down list using data validation. The drop down list consists of the following sample...

1
2
3
4
C-1
C-2
C-3

My formula looks like so..

Please Login or Register  to view this content.

The problem I have is that not all of the cells will have a value. But if the cell is blank, then I get a #value error. The formula works if I enter zeros in the blank cells, but I would like for this to work without having to enter zeros in the blank cells. Suggestions?

Hi onesNzeros,

welcome to the forum.

Please provide the sample workbook... thx


Regards,
DILIPandey
<click on below * if this helps>

Try

=S15-SUMPRODUCT(VALUE(SUBSTITUTE("0C-"&S18:S37,"C-","")))