Spreadsheet formulas for bookkeeping: SUM, IF and lookups

A spreadsheet is a grid of cells. A cell is named by its column letter, then its row number: C2 is the cell in column C, on row 2. A range is a block of cells, written as its first cell, a colon, then its last cell: C2:C5.

A formula is an instruction that tells the sheet to work something out. Every formula starts with an equals sign. Without it, the sheet treats what you typed as plain text.

A formula points at the cells that hold the figures. It does not have the figures typed into it. When a cell changes, every formula that points at it is worked out again. A typed figure stays as it was.

SUM

=SUM(C2:C5) adds every number in the range. In the books it totals a column: the net, VAT and gross columns of a day book, a column of the cash book, or a list of unpaid invoices. SUM skips text and empty cells.

IF

=IF(E2>0,"Chase","Paid") has three parts, split by commas: a test, what to show when the test is true, and what to show when it is false. Text goes in double quotes. Numbers do not. In the books it flags a line, such as an invoice that still has money owing.

VLOOKUP

=VLOOKUP(B2,$H$2:$I$4,2,FALSE) looks down the first column of a range for the value in B2, and returns the cell in column 2 of the same row. FALSE accepts an exact match only. In the books it fetches a customer's name from an account code, or a price from a product code.

XLOOKUP

It does the same job with one column to search and one column to return, and no column number to count. It is in Google Sheets, Excel 2021 and Microsoft 365, but not in older versions of Excel.

A formula can be copied down a column, so it is typed once and used on every row. Each cell reference moves down a row with it: =C2-D2 becomes =C3-D3.

A reference with dollar signs is locked. $H$2 always means cell H2. A lookup range is locked, so that it stays on the list when the formula is copied down.

This builds a list of sales invoices that shows what each customer still owes.

1. Give the list one heading row, then one invoice on each row. Keep one kind of detail in each column: invoice number, account code, amount, amount paid.
2. Type each account code the same way in the invoice list and in the customer list. A space after a code, or a number stored as text, stops a lookup finding it.
3. On the first row of figures, write the formula for what is owed: the amount less the amount paid. Point at the two cells.
4. Beside it, write the IF that flags the row.
5. Write the lookup that fetches the customer's name, with the range locked by dollar signs and FALSE as the last part.
6. Copy the three formulas down to the last invoice.
7. Under each money column, write a SUM. Read its range: it starts at the first invoice and ends at the last one, and it does not include its own cell.
8. Add a check cell that compares the totals, and make sure it returns 0.

Wicker Lane Catering keeps its March invoices in a sheet. Columns A to D are typed in. Columns E and F are formulas.

E2 holds =C2-D2 and is copied down. On row 4 it has become =C4-D4, and it returns 120.00: an invoice of £220.00 with £100.00 paid.

F2 holds =IF(E2>0,"Chase","Paid") and is copied down. Row 2 owes nothing, so the test is false and the cell shows Paid. Row 3 owes £145.50, so the test is true and the cell shows Chase.

The totals are on row 6. =SUM(C2:C5) returns 771.50, =SUM(D2:D5) returns 506.00 and =SUM(E2:E5) returns 265.50. That last figure is what customers owe Wicker Lane for March: its trade receivables.

To put a name beside invoice 5002, =VLOOKUP(B3,$H$2:$I$4,2,FALSE) finds K07 in column H and returns Reed Hall. A code that is not in column H returns the error #N/A, which means not available.

The check cell holds =SUM(C2:C5)-SUM(D2:D5)-SUM(E2:E5). Amount less paid less owed is 0, so the three totals agree.

• A SUM range that stops a row too soon. It happens when rows are added below a SUM that was written earlier.
• A total that includes its own cell. That is a circular reference, and Excel and Google Sheets both flag it.
• Typing figures into a formula. The formula cannot see a change in the cell it should have pointed at.
• Typing a figure over a formula. It looks just like the answer of a formula, and it never updates.
• Leaving the last part out of VLOOKUP. It then takes the nearest match, so a code that is not in the list can return a nearby row with no warning.
• Copying a lookup down without locking its range. The range slides down a row each time and drops the top of the list.
• Putting a number in quotes in an IF. In quotes it is text, and it will not add up.
• Hiding an error with IFERROR before finding its cause. IFERROR hides every error, including the ones you would want to see.

• Click each total and read its range. Check the first and the last cell every time.
• Add the figures two ways. A check cell that takes one grand total from the other returns 0 when they agree, and anything else means a total is wrong.
• Show the formulas in place of their answers, to find a typed figure where a formula should be. In Excel, choose Show Formulas on the Formulas tab. In Google Sheets, open the View menu, then Show, then Formulas.
• Test the IF with a row that passes the test, a row that fails it, and a row that sits exactly on it.
• Test the lookup with a code you know, and with one that is not in the list. The second must return #N/A, not a name.