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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,547
Office Version
  1. 365
Platform
  1. Windows
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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,618
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 22, 2018
Messages
135
Office Version
  1. 365
Platform
  1. Windows
As noted above, Ctrl Shift Enter has no effect. Nothing happens when I do it.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,618
Office Version
  1. 365
Platform
  1. Windows
Check my last reply again, it is likely that you missed the edit.
 

FrenchCelt

Board Regular
Joined
May 22, 2018
Messages
135
Office Version
  1. 365
Platform
  1. Windows
Aha, yes, I missed the edit. That finally worked. Thanks for the help!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,549
Messages
5,636,937
Members
416,953
Latest member
prakashkumar

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