Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel

Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel
Full audit trails to underlying transactions from P&Ls, Balance Sheets, graphs, PivotTables, and departmental TBs in a stand-alone Excel file. Aged Debtors and Aged Creditor files too. Free 30 day trials. Download today at www.onionrs.co.uk

Thursday 7 August 2014

Adding Debits and Credits in Excel without a helper column

Irritatingly, I keep coming across exports from accounting systems where the figures are always stated as absolute amounts with a separate column showing Dr or Cr to indicate the signage, Dr being + and Cr being -.

It isn't always convenient to add a helper column but a formula in the form of 
=SUMIF($A$1:$A$10,"Dr",$B$1:$B$10) -SUMIF($A$1:$A$10,"Cr",$B$1:$B$10) will subtract the sum of all the Cr values in column B from the sum of the Dr values in column B based on the Dr/Cr designations in column A.

I've noted that the range for SUMIF isn't necessarily a single column array. So, if you had Dr/Cr designators in columns A and C and amounts in columns B and D the formula could have the form =SUMIF($A$1:$C$10,"Dr",$B$1:$D$10) -SUMIF($A$1:$C$10,"Cr",$B$1:$D$10).