# Sum Cells with Text and Numbers

Microsoft Office 2007 home and student
May 4, 2010 at 06:05:03
Specs: Windows XP
 Hi,Hope someone can help here, E 400/400E 400/150W 400/150W 400/400W 400/400 5 I have a row of cells with letters and numbers what i am trying to do was add the cells with W in them once calculated then seeing how many 400 there are in that cell then adding up and getting a figure in cell of 5, as above are what ever number i have in the cell it will calculate hope this makes sense

See More: Sum Cells with Text and Numbers

#1
May 4, 2010 at 07:13:52
 re: I have a row of cells with letters and numbersIn your example, it looks like you have a column of cells with letters and numbers, so that is what I will try to help you with.Without using VBA, it would be very difficult to get the result you are looking for while leaving the letters and numbers in one cell.Here is what I would do:Let's assume your data is in A1:A5.1 - Select A1:A52 - Use Data...Text to Columns...Delimited...Next3 - Check the boxes for Space and Other, and put a / in the Other fieldThat should give you this:``` A B C 1 E 400 400 2 E 400 150 3 W 400 150 4 W 400 400 5 W 400 400 ```Then you could use this to get your answer:=SUMPRODUCT((A1:A5="W")*(B1:B5=400))+SUMPRODUCT((A1:A5="W")*(C1:C5=400))

Report •
Related Solutions