Summing with LEFT function, ignoring blank cells-CodePudding

I have a range that contains three digits-some text and there are some blank cells therefore receiving below message on image when I enter this formula:

=SUMPRODUCT(--LEFT(C12:C22;3))

Formula works just fine when I only select non-blank cells. Nonetheless, I would like it to contain blank cells as well.

I don't necessarily need sumproduct since I dont have other column, so I'm open to any solution.

CodePudding user response：

You could also use something like:

=SUMPRODUCT(--("0"&LEFT(C12:C22;3)))

CodePudding user response：

Two solutions:

Any Excel-Version: =SUMPRODUCT(--IF(C12:C22<>"",LEFT(C12:C22,3),0))

Excel 365 using Filter:

=LET(d,C12:C22,
dWithoutEmpty,FILTER(d,d<>""),
SUM(--LEFT(dWithoutEmpty,3)))