CODEWITHSUNDEEP
excelexcel-formulasumproductnonblank
Kerim Zorluk
Kerim Zorluk

Reputation: 3

Summing with LEFT function, ignoring blank cells

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))

enter image description here

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.

Upvotes: 0

Views: 247

Answers (2)

Rory
Rory

Reputation: 34045

You could also use something like:

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

Upvotes: 3

Ike
Ike

Reputation: 13024

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)))

Upvotes: 0

Related Questions