Sumproduct Left function ignore blank cells

[FryGirl]

How do I ignore the blank cells in this small range of five cells? I'm trying to sum the first three numbers in each cell.

=SUMPRODUCT(--(A2:A6<>""),LEFT(A2:A6,3))

Excel 2016 (Windows) 64 bit

	A​
2​	128/76​
3​	​
4​	118/76​
5​	​
6​	130/83​

Sheet: BP

 

[Tetra201]

Maybe

=SUMPRODUCT(--(A2:A6<>""),--LEFT(0&A2:A6,4))

 

[FryGirl]

Perfect, thanks.

 

[Rick Rothstein]

Maybe

=SUMPRODUCT(--(A2:A6<>""),--LEFT(0&A2:A6,4))

You do not need to test whether the cell is blank or not... you took care of that by concatenating a 0 onto the front of the cell values. Yoiur formula works fine when written like this...

=SUMPRODUCT(--LEFT(0&A2:A6,4))

 

[FryGirl]

Thanks Rick for the additional info. Works great.

 

[Tetra201]

Thank you Rick.