Summation of Number, Empty Cells and Cells with characters

Hi,

i have an issue and hope to find a solution with your support, I will try to be clear as much as possible,

A1=30
B1=empty cell
C1=15
D1=35 ?
E1=empty cell
F1=20
G1=50 ?

The above numbers are example so the pattern is different and may change, but the cells that contains the character “?” has the same format and text sequence, for example I will always have numbers then space then the character “?” such as “XX ?” or “x ?“ or “xxx ?” or “xxxx ?” etc…

I tried many formulas, either they sum the numbers only, either they sum the numbers and numbers with special character but return errors when including empty space, etc… I your help and support to create a formula to return the value 150.

I am using excel 2013.

Appreciate your prompt feedback.

Thank you for your kind support and nice cooperation.

Best regards

Thank you for your prompt reply, but unfortunately the proposed formula does not work beside I need a formula that work directly without the need of transforming the data into another temp cell.

This work when using B column then use the regular sum, but I can’t use a temp cell to clean up the original cell content, I need a direct formula to clean the content of the original cell and apply the sum on the fly without using temp cells.

Please advise

Both suggestions (icestationzbra and Haseeb A) worked perfectly, thank you so much.

Really appreciated.