5 Google Sheet Formulas Every Small Business Owner Needs
Managing a small business means handling data—sales totals, inventory levels, client lists, and expenses. Doing math by hand or copying data across tabs wastes time and causes mistakes.
Mastering five core Google Sheets formulas automates spreadsheet tasks and keeps numbers clear.
1. SUMIFS: Calculate Totals with Specific Conditions
Need total revenue for a single product line or total expenses in one month? SUMIFS adds numbers that meet multiple conditions.
Formula Structure:
=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
Real-World Example: Calculate total sales for "Consulting" services sold in "July":
=SUMIFS(C2:C100, A2:A100, "Consulting", B2:B100, "July")
2. XLOOKUP: Modern Way to Find Data Across Sheets
Forget VLOOKUP. XLOOKUP searches any column for a matching value and returns data from a different column, even if that column sits to the left.
Formula Structure:
=XLOOKUP(search_value, lookup_range, result_range, [missing_value])
Real-World Example: Find the unit price for a product based on its SKU code:
=XLOOKUP(A2, Products!A2:A50, Products!C2:C50, "Not Found")
3. QUERY: Build Custom Reports with Database Logic
QUERY brings SQL power into Google Sheets. Pull, filter, sort, and display specific columns from a master dataset into a clean sub-report without changing raw data.
Formula Structure:
=QUERY(data, query_string, [headers])
Real-World Example: Pull all unpaid invoices above $500, sorted by due date:
=QUERY(A1:E100, "SELECT A, B, C WHERE D = 'Unpaid' AND C > 500 ORDER BY E ASC", 1)
4. IMPORTRANGE: Connect Separate Spreadsheets
Stop copying data manually between different files. IMPORTRANGE pulls live data from one Google Sheets file directly into another.
Formula Structure:
=IMPORTRANGE(spreadsheet_url, range_string)
Real-World Example: Pull raw sales data from an external master ledger into an executive summary sheet:
=IMPORTRANGE("[https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID](https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID)", "Sales2026!A1:G100")
5. ARRAYFORMULA: Apply Formulas to Entire Columns Automatically
Adding new rows to a tracker often breaks calculations if formulas do not copy down automatically. Wrap an existing formula in ARRAYFORMULA to apply it down an entire column at once.
Formula Structure:
=ARRAYFORMULA(array_formula)
Real-World Example: Calculate total cost (Quantity * Unit Price) for every row in column C and D without dragging down formulas:
=ARRAYFORMULA(C2:C * D2:D)
Formula Cheat Sheet
| Formula | Best Used For | Primary Benefit |
|---|---|---|
| SUMIFS | Segmented totals | Quick financial breakdowns |
| XLOOKUP | Matching data across tables | Eliminates manual copy-paste |
| QUERY | Custom dashboards and filtered lists | Replaces complex formulas with simple logic |
| IMPORTRANGE | Syncing separate Google Sheets files | Keeps raw data private and connected |
| ARRAYFORMULA | Auto-populating entire columns | Prevents broken spreadsheet formulas |
Pro Tip: Combine
XLOOKUPandARRAYFORMULAto automatically auto-fill entire lookup columns whenever a new form response or sales entry arrives.