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

#### itr674

##### Well-known Member
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

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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

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

Replies
3
Views
203
Replies
7
Views
179
Replies
1
Views
98
Replies
3
Views
369
Replies
1
Views
404

1,217,383
Messages
6,136,249
Members
450,001
Latest member
KWeekley08

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back