Adding numbers and text

[Bluesquid619B]

***** 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

 

[T. Valko]

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.

 

[Ron Rosenfeld]

***** 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

This formula must be **array-entered**:

=SUM(IF(LEFT(A1:A8,1)="U",--MID(A1:A8,2,15),0))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.
--ron

 

[Terry Ciraulo]

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
















- Show quoted text -

What if the list is $10 for account "equipment" and then the next line
is $22 for account "office supplies" - how do you tell "if" to total
on another line all the "equipment" totals separate from the "office
supplies", etc.?

 

[T. Valko]

Show us some samples of your data and the desired result.

--
Biff
Microsoft Excel MVP

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
















- Show quoted text -

What if the list is $10 for account "equipment" and then the next line
is $22 for account "office supplies" - how do you tell "if" to total
on another line all the "equipment" totals separate from the "office
supplies", etc.?