Forum Discussion
How to Sum Cells with Text and Numbers in the Same Cell
Good Evening All -
I am struggling to get a formula to work in Excel 365 that totals the numbers in a row of cells that have letters in the same cells. The cells have one of four letters (V / F / I / D) followed by a quantity of hours (ex. V4 / F8 / etc.). In each row, there are a combination of each type of hour. At the end of each row, I need to be able to calculate the total number of hours correlating to each type (total V hours / total F hours / etc.).
Is this possible? Any help would be greatly appreciated! Thank you!
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.
3 Replies
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.
- Hello,
A classical solution is answered in the link below
https://www.extendoffice.com/documents/excel/2459-excel-sum-cells-with-text-and-numbers.html
