Find sum of a range of cells having both numbers and text

Try this

=SUMPRODUCT(SUBSTITUTE($F$2:$F$8," GM","")+0)

But instead of inputting GM as Text in cells, use it as formatting.. Which will display GM, but not affect calculations..

Select Cells > Ctrl+1 > Custom > 0" GM"

Hi,

I think, it's more efficient to use numbers instead of text. In excel is possible to create custom number formats. So, the "20 GM" can be number.
I attached the Book after changing the text cells to numbers.
You can do it by finding and replacing all " GM" with an empty string. Then right click in the cells -> Format Cells -> Number tab -> Custom -> #.##0" GM"

Book1_.xlsx