Summing Text & Numbers
 

I need to sum a range of cells that will contain both text and numbers. The
text will always be an "s" and the number will be a number between 0.25 and
10, in multiples of 0.25. The entry in the cells to be counted will be
entered as "s.25" or"s.5.50", for example, and the number following the "s."
needs to be totaled.

There will be some blank cells in the range.

Thanks

Summing Text & Numbers
 

I know it sounds Ridiculous but at this time of the day and assuming the
range of values is A1:A20 - try this:
{=SUM(VALUE(RIGHT(A1:A20,LEN(A1:A20)-(LEN(A1:A20)-LEN(SUBSTITUTE(A1:A20,".",""))))))}
*** This is an array formula, and is to be entered with CTRL+SHIFT+ENTER
rather than with simply ENTER.
The curly brackets {} are not to be typed manually, those are entered by the
€œExcel€�, when the formula is entered as an Array formula ***
Micky


"bgcooker" wrote:

I need to sum a range of cells that will contain both text and numbers. The
text will always be an "s" and the number will be a number between 0.25 and
10, in multiples of 0.25. The entry in the cells to be counted will be
entered as "s.25" or"s.5.50", for example, and the number following the "s."
needs to be totaled.

There will be some blank cells in the range.

Thanks

Summing Text & Numbers
 

the number will be a number between 0.25
and 10, in multiples of 0.25.
The entry in the cells to be counted will be
entered as "s.25"

So, that "s.25" has the value of 0.25 and not 25, right? If that's correct
then this will be rather complicated to do.

You can make this really simple by not combining text and numbers. Keep the
text in one cell and the number in another cell.

--
Biff
Microsoft Excel MVP


"bgcooker" wrote in message
...
I need to sum a range of cells that will contain both text and numbers. The
text will always be an "s" and the number will be a number between 0.25
and
10, in multiples of 0.25. The entry in the cells to be counted will be
entered as "s.25" or"s.5.50", for example, and the number following the
"s."
needs to be totaled.

There will be some blank cells in the range.

Thanks