Sum numbers in cells with text and symbols

FrenchCelt

Board Regular
Joined
May 22, 2018
Messages
135
Office Version
  1. 365
Platform
  1. Windows
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:

1612701404420.png


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
Joined
Dec 30, 2008
Messages
12,659
Office Version
  1. 365
Platform
  1. Windows
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))
 
Solution

FrenchCelt

Board Regular
Joined
May 22, 2018
Messages
135
Office Version
  1. 365
Platform
  1. Windows
It didn't work for me. The formula output the same value as in B23.
 

FrenchCelt

Board Regular
Joined
May 22, 2018
Messages
135
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

FrenchCelt

Board Regular
Joined
May 22, 2018
Messages
135
Office Version
  1. 365
Platform
  1. Windows
Here is today's version where I plugged in the formula:

1612711693016.png
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,659
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
56,832
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 22, 2018
Messages
135
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

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

Disable AdBlock

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
Top