Sum Cells with Numbers/Numbers & letters

[Mattandy55]

I have a range of cells A1: A20, I want to be able to sum that range, but some cells will be blank, some cells will only be a number and some cells will have a letter L at the end. I need to sum that range given all of those conditions. Any help on formula? I have tried multiple iterations that I have found online, but the only results I get are #Value.

Thanks in advance

 

[C Moore]

if it's only ever the L then the following should sum
=SUM(IFERROR(SUBSTITUTE(range,"L","")*1,0))
If there are other letters it will zero out the cell, so manually check a couple.

 

[awoohaw]

what version of excel do you use?

 

[awoohaw]

deleted

 

[Mattandy55]

if it's only ever the L then the following should sum
=SUM(IFERROR(SUBSTITUTE(range,"L","")*1,0))
If there are other letters it will zero out the cell, so manually check a couple.

Thank you so much for your help! This worked like charm, thank you!