Calculate SUMIF on a Text String

[legalhustler]

I have the following example of a various series of text strings (which includes spaces as you can see) which I would like to perform a SUMIF on:

201506 042 2473000101000000000000002196975429CB G0990000

I have the following formula in bold which when I evaluate shows the dollar amount, the criteria range (after the bold) when I evaluates shows the six digit account number:

=SUMIFS(IF(MID(A6:A13,Rules!$I$14,Rules!$G$14)="D",VALUE(CONCATENATE(LEFT(MID(A6:A13,Rules!$I$13,Rules!$G$13),Rules!$G$13-2),".",RIGHT(MID(A6:A13,Rules!$I$13,Rules!$G$13),Rules!$G$13-(Rules!$G$13-2)))),VALUE(CONCATENATE(LEFT(MID(A6:A13,Rules!$I$13,Rules!$G$13),Rules!$G$13-2),".",RIGHT(MID(A6:A13,Rules!$I$13,Rules!$G$13),Rules!$G$13-(Rules!$G$13-2))))*-1),MID(A6:A13,29,6),K6)

However, the above formula does not allow me to return a value because a error message pops that says "We found a problem with this formula. Try clicking Insert Function on the Formula tab to fix it......."

I am not sure what I am doing wrong. I am guessing that the dollar amount has no associated column with its account number, since the first part of the formula is only returning the dollar amounts. I'm not sure how to fix this or get it to calculate the SUMIF. Can someone please help? TIA!

 

[SteveO59L]

think that I would be inclined to separate the numeric from your text string, rather than have a very long formula

 

[XOR LX]

Hi.

Any ranges passed to COUNTIF(S)/SUMIF(S) must be references to actual worksheet ranges.

Perhaps if you explain exactly what you are trying to do with these text strings then we can look at alternative solutions.

Regards

 

[ukmikeb]

Hi

The only assumptions from your explanation that I could make are that cell G14 in Rules is 1 and that the example string if it is in column A is unrepresentative.

Please let us know what the values of the cells in Rules G13, G14, I13 and I14 and K6 and at least samples of 8 strings and the expected result/s.

Thank you.

 

[legalhustler]

Hi.

Any ranges passed to COUNTIF(S)/SUMIF(S) must be references to actual worksheet ranges.

Perhaps if you explain exactly what you are trying to do with these text strings then we can look at alternative solutions.

Regards

Below is a sample of my data and an explanation of what I would like to happen. I made my notes in red. I would like to not have the helper columns and have my extraction formula include it. My other issue is when extract unique GL's it's not picking up the blank/empty GL. Thanks for the help.

You should be able to download the sample Excel data by clicking the "Down arrow" on the right side once you click the link.

https://app.box.com/s/8zenh1s1eg5arrlzk34gyh013ao8rrxa

 

[legalhustler]

Hi

The only assumptions from your explanation that I could make are that cell G14 in Rules is 1 and that the example string if it is in column A is unrepresentative.

Please let us know what the values of the cells in Rules G13, G14, I13 and I14 and K6 and at least samples of 8 strings and the expected result/s.

Thank you.

I provided sample data in the link which you can download. Please let me know if you have any further questions. Thx.

 

[krykm]

It seems to be giving the expected results but why you really wanna avoid ref columns? As a result you get nested ugly formulas. Anyway hope helps.

Extract Without Ref Column

 

[ukmikeb]

Hi

With regard to extracting dollar values as per post #1 and SteveO59L's comment in post #2, consider the following approach :-
TB

	K	N
6	000150025D	1500.25
7	000150025C	-1500.25

<thead>
</thead><tbody>
</tbody>

Excel 2007

Worksheet Formulas Cell Formula N6 =MID(K6,1,9)/100*(IF(RIGHT(K6)="C",-1,1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Your formulae could be a lot shorter!

 

[legalhustler]

It seems to be giving the expected results but why you really wanna avoid ref columns? As a result you get nested ugly formulas. Anyway hope helps.

Extract Without Ref Column

Amazing. Thank you so much! The reason I want to get rid of the helper columns is because it's unnecessary space, improve calculation speeds, and I'm going to build a drop down validation that will be based on a nested IF statement.

 

[krykm]

Your welcome.

Thanks to ukmikeb, he highlighted a nice point.

Dividing 100 is really wisely.

You can change the formula in D8 as;

=IFERROR(SUM(IF(C8=IF(COUNTIF(Rules!$E$3:$E$6,MID($A$7:$A$16,Rules!$I$3,Rules!$G$3))>0,MID($A$7:$A$16,Rules!$I$3,Rules!$G$3),""),(MID($A$7:$A$16,Rules!$I$4,Rules!$G$4)/100)*IF(MID(VAATB,56,1)="D",1,-1))),"")

So that will be exactly faster than mine.

Regards