Sum numbers in cells with text and symbols

[FrenchCelt]

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]

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

 

[FrenchCelt]

Ctrl Shift Enter does nothing.

 

[jasonb75]

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]

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

 

[jasonb75]

Check my last reply again, it is likely that you missed the edit.

 

[FrenchCelt]

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