Sum - want to sum just the number in a column that has data

[itr674]

I have "p#" in column a and "b#" in column b. The "p" represents presure and 'b" represents burning. The "#" represents a number that is between 1 and 10.

I have 100 rows and use columns a and b. What is the formula for summing just the number in the cell a1 : b100?

Hope everyone can understand this I am at work and can't use Coolo ...
This message was edited by em on 2002-09-06 05:56
This message was edited by em on 2002-10-20 01:24

 

[eliW]

On 2002-09-06 05:55, em wrote:
I have "p#" in column a and "b#" in column b. The "p" represents presure and 'b" represents burning. The "#" represents a number that is between 1 and 10.

I have 100 rows and use columns a and b. What is the formula for summing just the number in the cell a1 : b100?

Hope everyone can understand this I am at work and can't use Coolo ...
This message was edited by em on 2002-09-06 05:56

Hi em,

Try the magic word "SUMPRODUCT"

=SUMPRODUCT((MID(A1:B100,2,2)*1))

Eli

 

[Dave Patton]

I read the question as how to sum column A or B ignoring the Text. If this is not what you require, advise or ignore the following.

A concise example often makes a question much clearer.

3 ideas

1. extract the number in an adjacent column and then sum
a) =RIGHT(LEN(A1)-1) or
b) =substitute


2. Array enter (Ctrl-Shift-Enter) the following

=SUM(1*(0&TRIM(SUBSTITUTE(A1:A100,"p",""))))

3. record the information as numbers and add
the text part with custom format
This message was edited by Dave Patton on 2002-09-06 07:06