SUMIFS totals by account in Excel

A ledger lists transactions one line at a time. Most of the questions people ask about it are totals: how much went on delivery this month, how much each department spent on repairs, how many bills were large enough to need a second look. Conditional sums and counts answer all three. This page shows how, with a worked example.

What a conditional sum does

An ordinary SUM adds every number in a range. A conditional sum adds only the numbers on rows that pass a test, such as "the account is Delivery Expense". You give it three things: the range to test, the value to look for, and the range to add.

There are two versions, and both appear in this lesson:

• SUMIF takes one condition. Its order is test range, criterion, sum range: =SUMIF(range,criterion,sum_range).
• SUMIFS takes one or more conditions. The range to add comes first, then pairs of test range and criterion: =SUMIFS(sum_range,range1,criterion1,range2,criterion2).

The argument order is the part people trip over. In SUMIF the amounts come last; in SUMIFS they come first. If you only learn one, learn SUMIFS: it handles one condition as well as five, so you never need to remember two orders.

Counting works the same way. COUNTIFS takes the same pairs of range and criterion, with no sum range, and returns how many rows pass every test.

This lesson is the step before a full trial balance. The trial balance in Excel page uses the same functions on a ledger with separate Debit and Credit columns and turns the totals into balances. Here the ledger has a single Amount column, and the focus is on the totals themselves: one condition, two conditions and a comparison.

The MO-220 objective this covers

These exercises are MO-220 practice for "Prepare a trial balance", under "Organize financial data with formulas". The objective is about choosing math, statistical and logical functions to summarize accounting data. MO-220 task instructions describe the purpose of a calculation, for example "show the total of each account's ledger amounts", rather than naming the function. So the real skill is reading a task and recognizing that it calls for a conditional sum. The exercises here are worded that way too. Accounting Exercises is not affiliated with Microsoft or Certiport.

Worked example: total each expense account

Northside Print Shop's October 2026 expense ledger has 20 lines in rows 4 to 23: date in column A, account in B, department in C and amount in D. Here are the first eight lines:

The account names to total are in H4:H8: Advertising Expense, Supplies Expense, Utilities Expense, Repairs Expense and Delivery Expense. The task asks for each account's total in column I, using one formula that can be filled down, and a grand total in I9.

Step 1: write the formula for the first account

In I4, the question is "add column D where column B equals the name in H4". With one condition, the solution uses SUMIF:

=SUMIF($B$4:$B$23,H4,$D$4:$D$23)

Read it left to right: test B4:B23, look for the text in H4, and add the matching amounts from D4:D23. Advertising Expense appears on rows 9, 10 and 20, so the result is 153.54 + 727.41 + 124.02 = 1,004.97.

The SUMIFS version gives the same answer, with the amounts moved to the front: =SUMIFS($D$4:$D$23,$B$4:$B$23,H4). The exercise is graded on the values in column I, so either is accepted.

Step 2: lock the ledger, leave the criterion free

The dollar signs matter. When you fill I4 down to I8, the ledger ranges must stay on rows 4 to 23, while the criterion must move from H4 to H5, H6 and so on. So the ranges are absolute ($B$4:$B$23) and the criterion is relative (H4). The absolute cell references page explains the dollar sign in more depth.

Step 3: fill down and total

In I9, =SUM(I4:I8) gives 13,135.92. That is also the sum of all 20 amounts in D4:D23, which is your check: if every ledger line belongs to one of the five accounts, the account totals must add up to the ledger total. If they come up short, some line has an account name that doesn't match any name in column H, often because of a stray space or a different spelling.

Two conditions: account and department

The second exercise uses Maple Lane Catering's December ledger, with the same layout. Now the summary is a grid: accounts down H4:H8, departments across I3:K3 (Sales, Operations, Admin). Each cell needs two conditions, so this is a job for SUMIFS. In I4:

=SUMIFS($D$4:$D$23,$B$4:$B$23,$H4,$C$4:$C$23,I$3)

The new idea is the mixed reference. $H4 locks the column but not the row, so it always reads the account from column H, whichever column you fill into. I$3 locks the row but not the column, so it always reads the department from row 3, whichever row you fill into. With those two references, one formula fills the whole grid right and down.

Zeros are correct here. No Admin line was coded to Advertising Expense, so that cell has nothing to add. To check a row, add it across: Advertising Expense is 2,237.09 + 2,212.96 + 0.00 = 4,450.05, which is that account's total for the whole ledger.

Counting and totaling above a review limit

The third exercise, Harbor Bakery's January ledger, asks a reviewer's question. Any bill of 450.00 or more needs a second look. The limit is typed once, in I1, and for each account you show the number of lines (column I), the number of lines at or above the limit (column J) and their total (column K).

The line count needs one condition: =COUNTIFS($B$4:$B$23,H4). The other two add a comparison on the Amount column. In J4 and K4:

=COUNTIFS($B$4:$B$23,H4,$D$4:$D$23,">="&$I$1)

=SUMIFS($D$4:$D$23,$B$4:$B$23,H4,$D$4:$D$23,">="&$I$1)

A comparison criterion is text: the operator in quotes, joined with & to the cell that holds the limit. ">="&$I$1 becomes ">=450" when the formula runs. Because it points at I1, changing the limit to 1,000 updates every count and total at once.

Repairs Expense shows how "at or above" works. It has five lines: 450.00, 144.21, 1,237.70, 361.51 and 718.09. Three are at or above 450.00 (450.00, 1,237.70 and 718.09), so J7 is 3 and K7 is 2,405.79. The 450.00 bill sits exactly on the limit and is included, because >= means "greater than or equal to".

Common mistakes

Ledger ranges that move when you fill down

Without dollar signs, =SUMIF(B4:B23,H4,D4:D23) in I4 becomes =SUMIF(B8:B27,H8,D8:D27) by I8. The ledger window slides down and drops the top rows. For Northside, Delivery Expense then shows 4,177.92 instead of 5,049.29, because the two Delivery lines in rows 4 and 5 (871.37 together) fall outside the range, and I9 drops to 12,264.55. Lock the ledger ranges.

Summing every line for every account

If the condition is left out, each account shows the whole ledger, 13,135.92, and the "total" in I9 is five times too big. Five identical numbers in a column of account totals are a sign the criterion isn't doing its job.

Headers that drift in a two-way grid

In the account-and-department grid, writing H4 and I3 without the mixed dollar signs means filling right shifts the account to column I, and filling down shifts the department to row 4. The formulas then compare against amounts or blank cells, and the grid fills with wrong totals. Use $H4 and I$3.

Forgetting the second condition

If the grid only tests the account, every department column repeats the account's full total. For Maple Lane, Advertising Expense would show 4,450.05 under Sales, Operations and Admin alike. Identical numbers across a row usually mean the department condition is missing.

Leaving out the line exactly at the limit

Using ">" instead of ">=" skips Harbor Bakery's 450.00 repair bill. J7 drops to 2 and K7 to 1,955.79. When a task says "at or above", the boundary is included.

Putting the cell reference inside the quotes

Writing ">=$I$1" looks reasonable, but everything inside quotes is literal text. The criterion asks for amounts greater than or equal to the text "$I$1", which no amount matches, so every count and total in columns J and K comes out 0. Keep the operator in quotes and join the cell outside them: ">="&$I$1.

Checking your work

Conditional sums fail quietly: a wrong formula still returns a number. A few habits catch most problems.

• Tie to the ledger total. The account totals should add up to SUM of the whole Amount column. In a grid, the grand total of every cell should match too.
• Check one cell by hand. Filter the ledger to one account and compare the visible amounts with your formula's result.
• Watch for patterns. Repeated numbers, totals that shrink as you go down, or a column of zeros all point to a reference problem rather than real data.

For simpler totals without conditions, see SUM, AVERAGE and COUNT for accounting. When the totals are ready, the trial balance in Excel page shows how to turn debit and credit totals into balances and prove they agree.