Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Let's say I have formula result that returns this +10+5+1+40, as text. I want to then sum the those numbers to get the total.

The result could be any number of values, so like +2+5 or +10+5+7+1+8.

In case it is helpful, the original data set is something like this S10_S5_S1_S40 or this E_E_S2_S5. I have stripped out everything except the numbers next to S, which is what I want to add.

Lastly, I am posting on superuser as I want to avoid VBA if I can.

Update

Through this formula: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D25,"E",""),"_",""),"S","=",1),"S","+")

I've gotten it down to this =10+5+1+40, where D25 = S10_S5_S1_S40 ... but that is really the same as having a + in front, I guess!

share|improve this question
up vote 2 down vote accepted

Assuming that you only have 1 or 2 digit numbers as per your examples then this "array formula" will sum all the "S" numbers from D25

=SUM(IF(MID(D25,ROW(INDIRECT("1:"&LEN(D25))),1)="S",(0&SUBSTITUTE(MID(D25,ROW(INDIRECT("1:"&LEN(D25)))+1,2),"_",""))+0))

confirmed with CTRL+SHIFT+ENTER

so if D25 contains S10_S5_S1_S40 that will give you the result 56

share|improve this answer
    
Pure awesomeness! Changing my answer because even though this is a bit of long, nasty formula, it's easier to implement than the Evaluate in the named range. – Scott Holtzman Feb 28 '13 at 22:11

There is no worksheet formula that does that. You can use the old Excel 4 macro function EVALUATE(), but it can only be used in a named range.

So, if you have data in column D, and you have the substitute formula in column E, for example, select F25 and create a new named formula with the Name Manager. Give it the name "EvaluateMe" and let it refer to

=EVALUATE(Sheet1!E25)

Then, enter

=EvaluateMe 

into cell F25 and the result will display. This named range uses a relative reference, and if the active cell was in F25 when you defined it, it will always try to evaluate the cell directly to the left.

share|improve this answer
    
Suh-Weet! THanks for this. I was looking for the evaluate function, but didn't really now how it works. Good, good stuff. Thanks – Scott Holtzman Feb 28 '13 at 4:31

This derives from Barry’s “awesome” answer.  The following “array formula”

=SUM(IF(MID(D25,ROW(INDIRECT("1:"&LEN(D25))),1)="S",MID(D25,ROW(INDIRECT("1:"&LEN(D25)))+1,IFERROR(FIND("_",D25,ROW(INDIRECT("1:"&LEN(D25)))+1),LEN(D25)+1)-(ROW(INDIRECT("1:"&LEN(D25)))+1))+0))

(entered with Ctrl+Shift+Enter) will add all the ‘S’ numbers in cell D25, regardless of length; so if D25 contains “S10_S5_S1_S240”, the above formula will give you the result 256.  This will also handle negative numbers: “S10_S5_S–1_S240” will yield 254.

I have not tested this exhaustively.  I’ll probably find something wrong with it Monday morning, myself.  :)

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .