10 Google Sheets Formulas Every Small Business Owner Should Know

Most small business spreadsheet problems can be solved with fewer than 20 formulas. Here are the 10 that do the most work.

1. SUMIF — Sum by Category

Add up numbers that match a condition.

Use case: Sum all expenses in the “Meals” category.

=SUMIF(B:B, "Meals", C:C)

B:B is your category column, “Meals” is the criteria, C:C is the amount column.

Variation: SUMIFS for multiple conditions (e.g., Meals in Q1):

=SUMIFS(C:C, B:B, "Meals", A:A, ">="&DATE(2024,1,1), A:A, "<="&DATE(2024,3,31))

2. VLOOKUP — Pull Data from a Reference Table

Look up a value in one column and return a corresponding value from another column.

Use case: Look up a product code and return its price.

=VLOOKUP(A2, ProductTable!A:B, 2, FALSE)

A2 = what you’re looking up, ProductTable!A:B = where to look, 2 = return the 2nd column, FALSE = exact match.

Note: INDEX/MATCH is more flexible and doesn’t break when columns are inserted, but VLOOKUP is fine for simple lookup tables.

3. IF — Make Decisions

Return different results based on a condition.

Use case: Flag invoices that are past due.

=IF(D2<TODAY(), "OVERDUE", "Current")

D2 is the due date column. If the due date is before today, return “OVERDUE,” otherwise “Current.”

4. COUNTIF — Count Items Meeting Criteria

Use case: Count how many invoices are unpaid.

=COUNTIF(E:E, "Unpaid")

5. TEXT — Format Dates and Numbers

Convert numbers to formatted text for reports and display.

Use case: Format a date as “January 2024” for a report header.

=TEXT(A2, "MMMM YYYY")

Format a number as currency with commas:

=TEXT(B2, "$#,##0.00")

6. EDATE — Calculate Future Dates

Add months to a date.

Use case: Calculate when a 30-day invoice is due.

=EDATE(A2, 1)

A2 is the invoice date; adding 1 returns the date exactly one month later.

For days-based terms:

=A2+30

7. ROUND and ROUNDUP — Avoid Penny Errors

Financial calculations often produce floating-point errors. Always round money.

=ROUND(A2*0.15, 2)

Rounds to 2 decimal places. ROUNDUP rounds up, ROUNDDOWN rounds down.

8. IFERROR — Handle Errors Gracefully

When a formula might produce an error (divide by zero, VLOOKUP not found), wrap it.

=IFERROR(B2/C2, 0)

Returns 0 instead of #DIV/0! when C2 is empty or zero.

9. QUERY — Filter and Aggregate Data

The most powerful Google Sheets-specific function. Use it like a SQL query on your data.

Use case: Show all expenses over $500 from Q1, sorted by amount.

=QUERY(A:D, "SELECT A,B,C,D WHERE D > 500 AND A >= date '2024-01-01' AND A <= date '2024-03-31' ORDER BY D DESC", 1)

QUERY reduces the need for many SUMIF/COUNTIF combinations by letting you filter and reshape data in one formula.

10. ARRAYFORMULA — Apply a Formula to an Entire Column

Instead of copying a formula down every row, ARRAYFORMULA applies it to every row automatically.

Use case: Calculate commission on every row without copying the formula.

=ARRAYFORMULA(IF(A2:A<>"", B2:B*0.05, ""))

A2:A<>"" checks that the row isn’t empty before calculating. New rows auto-populate with the formula.

Combining These Formulas

Most useful financial spreadsheets combine several of these:

Dynamic P&L summary:

• SUMIF to total revenue and expenses by category
• TEXT to display the current month as a label
• IFERROR to prevent errors when categories are empty
• IF to highlight categories over budget

Invoice tracker:

• EDATE to calculate due dates from invoice date
• IF to flag overdue invoices
• COUNTIF to count unpaid invoices
• SUMIF to total unpaid amounts by customer

Practice building one formula at a time. The formula bar in Google Sheets shows you the result as you type — use it to test your logic before committing to a full spreadsheet.