Forum Discussion
How to Sum Cells with Text and Numbers in the Same Cell
Just for the fun of creating a formula that works in the situation you described, I came up with this:
=SUMPRODUCT(--(LEFT($A2:$F2,1)=H$1)*--(RIGHT($A2:$F2,LEN($A2:$F2)-1)))The attached workbook has a working example. Obviously, you need to adjust the cell references to your own situation. But, I must agree with mathetes that it is not good practise to combine letters and numbers into one cell if you intend to do calculations with the numbers afterwards. Possible, but not very practical.
Just for the fun of creating a formula that works in the situation you described, I came up with this:
=SUMPRODUCT(--(LEFT($A2:$F2,1)=H$1)*--(RIGHT($A2:$F2,LEN($A2:$F2)-1)))The attached workbook has a working example. Obviously, you need to adjust the cell references to your own situation. But, I must agree with mathetes that it is not good practise to combine letters and numbers into one cell if you intend to do calculations with the numbers afterwards. Possible, but not very practical.