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

#### Fluff

##### MrExcel MVP, Moderator
Ok, thanks for updating your profile.
As you don't have the dynamic array functions you will need to confirm Jason's formula with Ctrl Shift Enter

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### FrenchCelt

##### Board Regular
Ctrl Shift Enter does nothing.

#### jasonb75

##### Well-known Member
Have you tried array confirming the formula?

If you don't have Unique yet then it is possible that you are too far behind on updates for it to work without array confirmation.

Edit:- last 2 replies were not showing for some reason.

If you only select the cell then press Ctrl Shift Enter then it will still not work, you need to go into edit mode with the cell (i.e so that you can see the formula in the cell, not the current result).

#### FrenchCelt

##### Board Regular
As noted above, Ctrl Shift Enter has no effect. Nothing happens when I do it.

#### jasonb75

##### Well-known Member
Check my last reply again, it is likely that you missed the edit.

#### FrenchCelt

##### Board Regular
Aha, yes, I missed the edit. That finally worked. Thanks for the help!

Replies
0
Views
148
Replies
2
Views
117
Replies
4
Views
108
Replies
3
Views
444
Replies
0
Views
127

1,129,700
Messages
5,637,882
Members
416,988
Latest member
Ahmed_Yehia

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