Adding numbers and text
 

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

Adding numbers and text
 

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

Adding numbers and text
 

On Fri, 22 May 2009 14:16:00 -0700, Bluesquid619B
wrote:

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

Adding numbers and text
 

On May 22, 2:46*pm, "T. Valko" wrote:
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- Hide quoted text -

- 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.?

Adding numbers and text
 

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

--
Biff
Microsoft Excel MVP


"Terry Ciraulo" wrote in message
...
On May 22, 2:46 pm, "T. Valko" wrote:
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- Hide quoted text -

- 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.?