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?

 

[jasonb75]

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
	B	C	D
9	1	2	3
10	(1N	1M	1T)
11	(6N	6T)
12		(6N	6T)
13
14
15
16
17
18
19
20
21
22	11	6	9
23	18	19	16
Sheet6

Cell Formulas
Range	Formula
B22:D22	B22	=B23-SUMPRODUCT(IFERROR(--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B10:B21,"(",""),")",""),"N",""),"M",""),"T",""),0))

 

[FrenchCelt]

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

 

[jasonb75]

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]

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

 

[FrenchCelt]

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

 

[jasonb75]

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]

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]

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]

We don't have Unique available yet.