Try this array formula** :

Assumes that if a cell contains a letter it also contains a number.

=SUM(IF(LEFT(A1:A8)="u",--MID(A1:A8,2,10)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Bluesquid619B" wrote in message
...
***** I am using Excel 2003 and Excel 2007 *****

I have an array that contains many entries like this:

U4
U4.6
U1
P8
P2
V9
V3
V3.5
and others.

The number is always preceeded by one consonant (one text letter from A -
Z).

I want to add all the U numbers together for a total (I do not want to
count
how many times they occur, I need total the numbers after the U or other
single text character).

I want the result to be listed in the formula cell. In the above group of
numbers the total value of U should be "9.6".

I would also like to do the same with any of the text letters in the cells
-- P, V, etc.

I am thinking and trying something like these:

=SUM(IF(C38:N65="U",C38:N65))
=SUM(IF(C38:N47,"U",N(C38:N47)))

but get "#VALUE!" errors.

Basically, I want to strip the text character out and SUM the number.

Any help will be appreciate no matter how difficult the algorithm or
formula
is.

Thank you