Lesson 4: SUMIF: build a trial balance from journal lines

Practice exercises for accountants, checked instantly in your browser.

SUMIF adds only the rows that match a condition: =SUMIF(range to test, criterion, range to add). With journal lines in columns B to D, =SUMIF($B$2:$B$13,F2,$C$2:$C$13) adds every debit posted to the account named in F2.

Lock the two journal ranges with $ so they stay put when you fill down the account list. Leave the criterion F2 relative so each row picks up its own account. A trial balance is only useful if total debits equal total credits, so finish with a check that compares the two column totals.

Worked example: =SUMIF($B$2:$B$13,"Cash",$C$2:$C$13)
Total debits posted to Cash. In the exercises, point at the account cell instead of typing the name.

Exercises

Exercise 1

Copperleaf Design Co. posted the journal lines in A2:D13. In G2, total the debits for the account in F2, then fill to G8.

Exercise 2

Debits are done. In H2, total the credits for the account in F2 and fill to H8.

Exercise 3

Totals are in G9 and H9. In G10, return Balanced if total debits equal total credits, otherwise Out of balance.