5 Excel formulas every shop owner should know

You don't need hundreds of formulas. These five cover almost all of a shop's bookkeeping, stock control and sales summaries, and every one works in both Excel and Google Sheets.

The examples use a log like this: column A date, B description, C category, D income, E expense.

1. SUM: add numbers up

Totals a whole column or a selected range.

=SUM(D2:D500) → total income

Tip: make the range longer than your data, say to row 500, so new entries are counted without editing the formula.

2. SUMIFS: add up only what matches

The formula shop owners use most, for example to see how much you spent on ingredients.

=SUMIFS(E2:E500, C2:C500, "Ingredients")

The order is range to sum, then criteria range, criterion pairs. You can add several pairs, for example to total October 2026 only:

=SUMIFS(E2:E500, C2:C500, "Ingredients", A2:A500, ">="&DATE(2026,10,1), A2:A500, "<"&DATE(2026,11,1))

3. COUNTIF: count entries

Counts how many entries match a condition, such as unpaid bills.

=COUNTIF(F2:F500, "Unpaid")

Or count days with sales above 5,000: =COUNTIF(D2:D500, ">5000")

4. IF: let the sheet decide

Shows a result based on a condition, such as a warning when stock is at the reorder point (G = in stock, H = reorder point).

=IF(G2<=H2, "Reorder", "OK")

You can nest IFs, but past two or three levels they get hard to read; try IFS instead.

5. XLOOKUP: pull data from another table

Fetches a price or item name from your master list when you type only the item code.

=XLOOKUP(A2, Products!A:A, Products!C:C, "Not found")

Meaning: find A2 in column A of the "Products" sheet and return the value from column C; if there's no match, show "Not found".

On older Excel without XLOOKUP, use =VLOOKUP(A2, Products!A:C, 3, FALSE). Always end with FALSE so it looks for an exact match.