SUM, AVERAGE and COUNT in Excel for accounting

Almost every accounting spreadsheet ends with a few summary numbers: the total spent, how many transactions there were, the average size, the largest and the smallest. This page shows how to get each one with a single formula, how to handle voided entries, and how to roll a bank register forward to its ending balance.

Why accountants summarize a ledger

A ledger is a list. A list is hard to read. The person who asks for it usually wants a handful of answers: how much did we spend this month, how many payments went out, what was the biggest one? Those answers drive real decisions. A total goes into a report or gets compared with a budget. A count tells you whether a batch of invoices is complete. A largest amount flags something worth a second look.

You could add the numbers on a calculator, but that breaks the moment someone corrects a row. A formula reads the column every time the sheet changes, so the summary stays right. It also shows your work: a reviewer can click the cell and see exactly which range you added.

Real ledgers are also messy. Some entries get voided after they are written down. A common bookkeeping habit is to leave the row in place and replace the amount with a word such as VOID, so the record of the cancelled transaction is not lost. Your summary has to treat those rows correctly: they are not money and they are not transactions. Choosing the right function handles this for you.

The five functions you need

Each of these takes a range, such as D5:D19, and returns one number.

• =SUM(D5:D19) adds every number in the range. Text such as VOID and empty cells are ignored.
• =COUNT(D5:D19) counts the cells that hold numbers. Text and blanks are not counted.
• =COUNTA(D5:D19) counts every cell that is not empty, whether it holds a number or text. That is a different question, and usually the wrong one for a ledger with voids.
• =AVERAGE(D5:D19) adds the numbers and divides by how many numbers there are. Text and blanks are left out of both parts.
• =MAX(D5:D19) and =MIN(D5:D19) return the largest and the smallest number in the range.

The pattern is the same for all of them: one function name, one range. The skill is in picking the function that answers the question you were asked, and in pointing it at the whole column, no more and no less.

Worked example 1: total and count an expense ledger

Bluebird Veterinary keeps a monthly expense ledger with the date in column A, the vendor in B, the category in C and the amount in D. The data runs from row 5 to row 19, so there are fifteen rows. Three of them were voided. Here are a few of the rows:

The task asks for the total spending in G4 and the number of transactions in G5.

SUM skips the three VOID cells because they are text, so the total is only real spending. COUNT also skips them and returns 12: fifteen rows minus three voids. If you had used =COUNTA(D5:D19) instead, you would get 15, which counts cancelled entries as if they were payments.

Worked example 2: average, largest and smallest

Maple Lane Catering has the same layout: amounts in D5:D19, fifteen rows, two of them voided. A few rows:

The thirteen real amounts add up to 11,259.81. The summary:

AVERAGE divides 11,259.81 by 13, the number of numeric cells, which gives 866.14 when shown to the cent. Now look at a formula that seems reasonable but is not: =SUM(D5:D19)/ROWS(D5:D19). ROWS returns how many rows the range has, which is 15, voids included. The result is 11,259.81 divided by 15, or 750.65. That treats each voided entry as a transaction of zero and drags the average down. AVERAGE already does the right division, so let it.

Worked example 3: summarize a bank register

A bank register is a ledger with two amount columns. Northside Print Shop records deposits in column C and withdrawals in column D, one or the other on each row. The opening balance for the month sits in G3.

With an opening balance of 12,038.43 in G3, the summary block looks like this:

The ending balance is the opening balance, plus money in, minus money out: 12,038.43 + 25,032.95 − 21,177.48 = 15,893.90. Notice that G6 refers to the summary cells rather than repeating the ranges. That keeps the formula short and means a fix to either total flows through to the balance.

COUNT works neatly here because the blank cells in column D are deposit rows. Blanks are not numbers, so =COUNT(D5:D19) returns exactly the seven withdrawals.

Common mistakes and how to spot them

These are the errors that come up again and again when people summarize a ledger. The exercises in this lesson check for each one and tell you which it is.

Counting VOID entries with COUNTA

COUNTA counts anything that is not blank, so the word VOID counts as a transaction. In the Bluebird example it returns 15 instead of 12. If your count matches the number of rows exactly and you know some rows were voided, that is the giveaway. Use COUNT when you want the number of amounts.

A SUM range that stops one row early

Typing =SUM(D5:D18) when the data ends in row 19 leaves out the last transaction. For Bluebird that gives 17,300.56, short by exactly the last amount of 1,918.53. The sheet shows no error, which is what makes this mistake dangerous. Click the formula and check that the highlighted range covers the last row of data.

Averaging by dividing by all the rows

Dividing a total by the number of rows, whether with ROWS or by typing 15, counts voided rows as zero-amount transactions. For Maple Lane that gives 750.65 instead of 866.14. If you ever need to build an average by hand, divide by COUNT, not by the number of rows. Simpler still, use AVERAGE.

Swapping MAX and MIN

It sounds too simple to get wrong, but when the labels sit in one column and the formulas in another, it is easy to put MIN next to "Largest". A quick sense check helps: the largest transaction should be the bigger of the two numbers, and both should appear somewhere in the list.

An ending balance that ignores the opening balance

Writing =G4-G5 gives the net change for the month, not the balance. For Northside that is 3,855.47, which is the amount the account grew. The account did not start at zero, so the ending balance must start from G3.

Reversed signs

Writing =G3-G4+G5 subtracts the deposits and adds the withdrawals. For Northside that gives 8,182.96, which would mean the business lost money it actually received. Say it in words before you type it: deposits increase the balance, withdrawals decrease it.

Habits that keep summaries right

• Read the question, then pick the function. "How many transactions" means COUNT. "How many rows" might mean COUNTA or ROWS. Accounting questions are almost always about transactions.
• Point at cells, never type numbers. A formula like =19219.09 looks right today and is wrong tomorrow. The exercises reject hard-coded numbers for this reason.
• Leave voids as text. If you replace VOID with 0, COUNT and AVERAGE will treat the row as a real transaction. Text keeps it out of every calculation.
• Cross-check. AVERAGE times COUNT should equal SUM. The largest amount should be at least the average, and the smallest at most the average.

Where this fits in MO-220 practice

These functions are the basics that the rest of a spreadsheet builds on, and the MO-220 exam expects you to use them without hesitation. MO-220 is a 50-minute, performance-based exam [Official], so you work in a live workbook rather than answering multiple-choice questions. Its tasks describe what the result should be, such as the number of transactions, instead of naming the function [Official]. That is why this lesson phrases tasks the same way: you decide that "number of transactions, skipping voids" means COUNT. Accounting Exercises is not affiliated with Microsoft or Certiport.

Each exercise here gives you a fresh ledger in a Microsoft® Excel®-style grid. You type real formulas, the spreadsheet engine calculates them, and your results are checked against the reference values straight away.