Adding numbers and text

  • Thread starter Thread starter Bluesquid619B
  • Start date Start date
B

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
 
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.
 
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.
 
***** 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
Click to expand...

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
 
***** 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
Click to expand...

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
 
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 -
Click to expand...

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.?
 
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 -
Click to expand...

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