Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

3 Answers 3

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.