#### legalhustler

##### Well-known Member

- Joined
- Jun 5, 2014

- Messages
- 1,158

- Office Version
- 365

- Platform
- Windows

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:

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!

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: