# Sum numbers in cells with text and symbols

#### FrenchCelt

##### Board Regular
Hello,

I have a spreadsheet that we use for planning out workflow and part of that is to indicate where to place what we call Nose, Middle, and Tail loads (we consider them double/triple loads). On the spreadsheet we designate these as N, M, and T. We also bracket these in parenthesis. At the bottom of each section, I count the total number of single loads that aren't one of these N/M/T loads in my head, but would like to insert a formula to count these for me. I've looked around at various formulae meant to do something similar and I can't adapt to my needs. This is what the data looks like:

What I do is add up the 1 and 6 in B10 and B11, then subtract that total from the value in B23 to get 11 in B22. The same method is used in C and D to get 6 and 9 respectively. So the formula would need to sum those numbers while ignoring the letters and parentheses symbols and be subtracted from the value in Row 23. Is this possible?

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### jasonb75

##### Well-known Member
Something like this?

Note that the SUBSTITUTE function is case sensitive, so the inclusion of UPPER would be advised if your entry can be inconsistent. Also, it will most likely need to be array confirmed if you are using anything other than office 365.
sum sheets.xlsx
BCD
9123
10(1N1M1T)
11(6N6T)
12(6N6T)
13
14
15
16
17
18
19
20
21
221169
23181916
Sheet6
Cell Formulas
RangeFormula
B22:D22B22=B23-SUMPRODUCT(IFERROR(--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B10:B21,"(",""),")",""),"N",""),"M",""),"T",""),0))

#### FrenchCelt

##### Board Regular
It didn't work for me. The formula output the same value as in B23.

#### jasonb75

##### Well-known Member
That's what happens when it is not entered correctly.
it will most likely need to be array confirmed if you are using anything other than office 365.

#### FrenchCelt

##### Board Regular

I'm using 365 and I always use uppercase for the letters.

#### FrenchCelt

##### Board Regular
Here is today's version where I plugged in the formula:

#### jasonb75

##### Well-known Member

Then there must be something else hidden in the data that you haven't told us about, which is not visible from the screen captures. If the data is being imported from another application then there could be zero width spaces.

In the screen capture there are 3 characters in B10, if the test formula =LEN(B10) shows anything other than 3 then it means that such a problem exists in that cell.

As you can see from post 2, the formula works fine with 365. Array confirmed, it will work with all versions back to 2007.

#### Fluff

##### MrExcel MVP, Moderator
I'm using 365

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)

Do you have the Unique & Filter functions?

#### FrenchCelt

##### Board Regular
The data from B10:B21 is keyed in directly. B23 is keyed directly. =LEN(B10) returns 3. I created this little spreadsheet to help me with planning and I didn't do anything exotic. What you see is what you get.

#### FrenchCelt

##### Board Regular
We don't have Unique available yet.

Replies
0
Views
150
Replies
2
Views
118
Replies
4
Views
110
Replies
3
Views
445
Replies
0
Views
128

1,130,092
Messages
5,640,066
Members
417,125
Latest member
sfreind

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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