# Calculate SUMIF on a Text String

#### legalhustler

##### Well-known Member
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!

Last edited:

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
think that I would be inclined to separate the numeric from your text string, rather than have a very long formula

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

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.

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

Last edited:
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.

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

Hi

With regard to extracting dollar values as per post #1 and SteveO59L's comment in post #2, consider the following approach :-
TB
KN
6000150025D1500.25
7000150025C-1500.25

</tbody>
Excel 2007

Worksheet Formulas
CellFormula
N6=MID(K6,1,9)/100*(IF(RIGHT(K6)="C",-1,1))

</tbody>

<tbody>
</tbody>

Your formulae could be a lot shorter!

Last edited:
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.

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

Replies
1
Views
89
Replies
2
Views
204
Replies
3
Views
234
Replies
3
Views
161
Replies
16
Views
622

1,203,727
Messages
6,056,976
Members
444,899
Latest member
Excel_Temp

### 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.

### Which adblocker are you using?

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

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