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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

128/76​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

118/76​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

130/83​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: BP[/td][/tr][/table]

 

[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.