You have list of accounting postings. For some reason, you need to check that sum of debit postings equals to sum of credit postings. There might be an error in closing process, and you just want to double check in Excel that no mismatch has taken place in accounting. System should not allow a posting when debit differs from credit, but this is a case to really make sure that no mismatch exists.

Accounting data was simplified. Imagine, that you have hundreds of accounts and thousands of postings.

One simply option is to count with SUMIF. First, you need to count sum for every column like in row 10. Totals for debit and credit are counted with SUMIF. In SUMIF you need to first determine range for Ds and Cs that is B2:M2. Then criteria is needed whether it is D or C, B13 or B14. Finally you enter sum range B10:M10. If in row 2 the value equals to B13 or B14 then sum is counted from row 10. This works, but is you have lots of data, this might be bit difficult as you need to count each column sum separately.

Another bit easier solution is to count with SUMIF without interim SUMs. Write following formula =SUM(IF(B2:M2=B11;B3:M8)) in C11 and press control+shift+enter. The result should be {=SUM(IF(B2:M2=B12;B3:M8))}, but don’t write that, that is the outcome after control+shift+enter.

SUM is counted in M3:M8 if values in B2:M2 equals to B11. Don’t forget control+shift+enter.

Third option is to use column numbering.

COLUMN function is turning column letter to number.

In our case debits are in paired columns and credits in impaired.

Paired and impaired numbers can be traced by MOD function.

If MOD is 1 then the column is for debits and 0 for credits.

Row 10 includes sum of each row like M10 sums M3:M8. Row 9 determines whether the column number is paired or impaired. SUMIF in C12 is a normal SUMIF. First you need to feed the range meaning Ds and Cs, then criteria C or D is needed, last sum range is given.

Fourth option is based on paired and impaired column numbers. A custom made formula is summing up the values in paired or impaired columns. For paired cells dc parameter is 0 and impaired 1. Then activate the sum area. That’s all.

The custom made function:

Function z_dc(dc, rng As Range)

Dim cell As Range

Dim para

If dc = 0 Then

    para = 0

ElseIf dc = 1 Then

    para = 1

End If

z_dc = 0

For Each cell In rng

If (cell.Column) Mod 2 = para Then

    z_dc = (z_dc + cell.Value)

End If

Next cell

End Function