0

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.

JvdV
  • 70,606
  • 8
  • 39
  • 70
Kerim Zorluk
  • 3
  • 2

2 Answers2

3

You could also use something like:

=SUMPRODUCT(--("0"&LEFT(C12:C22;3)))
Rory
  • 32,730
  • 5
  • 32
  • 35
0

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)))
Ike
  • 9,580
  • 4
  • 13
  • 29