Thread: sum alphanumeric cells

You will need a helper column to split the transaction_amount. I used this formula
=LEFT(B2,LEN(B2)-4)*1
to convert it to a number. Once you have this, then you can do a sumif on this helper column.
Look here for how that works.
http://www.techonthenet.com/excel/formulas/sumif.php

Hi bicas,

Assuming you have this data in Column A, use following in column B and drag it down:-

=VALUE(SUBSTITUTE(TRIM(MID(A2,SEARCH("//",A2)+2,(SEARCH("EUR",A2)-SEARCH("//",A2)-2))),",",""))

Now for Debit sum, use following formula:-

{=SUM(IF(ISNUMBER(SEARCH("DEBIT",$A$2:$A$15)),$B$2:$B$15,""))}

and for Credit sum, use following formula:-

{=SUM(IF(ISNUMBER(SEARCH("CREDIT",$A$2:$A$15)),$B$2:$B$15,""))}

Debit and Credit formula are array ones and need to be entered using shift+ctrl+enter key combination.

I hope this solves your issue.


Regards,
DILIPandey

<click on below 'star' if this helps>

Assume Col_A is Contracts, Col_B is Transaction Amount, Col_C is Type.

So, try this

=SUM(IF(C$2:C$15="Debit",--(0&TRIM(SUBSTITUTE(SUBSTITUTE(LOWER(B$2:B$15),"eur",""),",","")))))

Confirmed with CTRL+SHIFT+ENTER.

Change Debit to Credit for Cr values.