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.
First of all, if this is going to be a continuing set of data, do all you can to stop collecting it in that manner. It's a classic case of bad design. Put the letter in a cell of its own; the number in adjacent. Then counting/totalling by category becomes very easy.
That said, even if it continues see attached for some formulas that create the arrangement described above. And then I've used Pivot Table to summarize the data as you want.