sum alphanumeric cells

**bicas** · 12-10-2011, 02:31 PM

hi, i have a sheet with a bunch of alphanumeric values and blanks and i need to sum and then make a diference of the values that show like debit.
below is an example of the data that i have, can you help me with a formula or macro to solve this.
Notice that there is same blank cells in the midle.
tks

CONTRACTS_SELECTED //TRANSACTION_AMOUNT //TRANSACTION_TYPE
08-12-2011 11:49 // 43,00 EUR // DEBIT
07-12-2011 18:51 // 243,50 EUR // CREDIT
07-12-2011 18:47 //4,00 EUR // CREDIT
07-12-2011 18:43 //145,86 EUR // CREDIT
07-12-2011 18:39 //797,48 EUR // CREDIT
07-12-2011 18:35 // 371,48 EUR// DEBIT
// - //
// - //
07-12-2011 18:30 //287,48 EUR// CREDIT
07-12-2011 18:25 //287,36 EUR// CREDIT

07-12-2011 18:04 //287,50 EUR// CREDIT
07-12-2011 17:52 //291,48 EUR// DEBIT
07-12-2011 17:51 //291,48 EUR// CREDIT

**alansidman** · 12-10-2011, 04:10 PM

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

**dilipandey** · 12-15-2011, 06:00 AM

Hi bicas,

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

Please Login or Register  to view this content.

Now for Debit sum, use following formula:-

Please Login or Register  to view this content.

and for Credit sum, use following formula:-

Please Login or Register  to view this content.

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>

**Haseeb Avarakkan** · 12-15-2011, 06:14 PM

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.