Essential Google Sheets Formulas for Business and Finance

Google Sheets has over 400 built-in functions, but you only need about 25 to handle virtually any business or financial task. This guide covers the essential formulas organized by category, with real-world examples showing exactly how to use each one. Bookmark this page as your reference - it covers everything from basic sums to advanced financial calculations.

Each formula entry includes the syntax, a practical example, and the business context where it is most useful. All examples use Google Sheets syntax, which differs slightly from Excel in a few cases (noted where applicable).

Math and Basic Functions

SUM

Syntax: =SUM(range)

Example: =SUM(B2:B100)

Use case: Totaling a column of expenses, income, or any numeric data. SUM is the most used formula in any financial spreadsheet. It ignores text and empty cells, so it works reliably even in partially filled columns.

SUMIF

Syntax: =SUMIF(range, criterion, sum_range)

Example: =SUMIF(C2:C100, "Groceries", D2:D100)

Use case: Totaling expenses in a specific category, income from a specific client, or sales of a specific product. SUMIF is the foundation of every budget dashboard that shows spending by category.

SUMIFS

Syntax: =SUMIFS(sum_range, criteria_range1, criterion1, criteria_range2, criterion2)

Example: =SUMIFS(D2:D100, C2:C100, "Marketing", A2:A100, ">=2026-01-01", A2:A100, "<=2026-03-31")

Use case: Totaling amounts with multiple filters, for example, marketing expenses in Q1 2026. Essential for building monthly or quarterly financial reports from a single transaction log.

AVERAGE

Syntax: =AVERAGE(range)

Example: =AVERAGE(D2:D50)

Use case: Calculating average invoice amount, average monthly spending, or average days to payment. Pair with AVERAGEIF for category-specific averages.

COUNTIF

Syntax: =COUNTIF(range, criterion)

Example: =COUNTIF(E2:E100, "Overdue")

Use case: Counting how many invoices are overdue, how many transactions fall in a category, or how many months exceeded budget. COUNTIFS adds multiple criteria.

ROUND / ROUNDUP / ROUNDDOWN

Syntax: =ROUND(value, places)

Example: =ROUND(B2*0.0765, 2)

Use case: Rounding currency calculations to two decimal places. Tax calculations and percentage-based fees often produce long decimals that need rounding for invoices and reports.

Lookup and Reference Functions

VLOOKUP

Syntax: =VLOOKUP(search_key, range, index, is_sorted)

Example: =VLOOKUP(A2, Clients!A:C, 3, FALSE)

Use case: Looking up a client's billing rate from a client reference table, finding a product price from an inventory sheet, or pulling a category description from a category list. Always use FALSE for the last parameter to ensure exact matches.

INDEX + MATCH

Syntax: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Example: =INDEX(C2:C100, MATCH("ACME Corp", A2:A100, 0))

Use case: A more flexible alternative to VLOOKUP. INDEX-MATCH can look up values to the left (VLOOKUP cannot), works with column insertions and deletions, and is generally faster on large datasets. Use this as your default lookup method for production templates.

XLOOKUP

Syntax: =XLOOKUP(search_key, lookup_range, return_range, [if_not_found])

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

Use case: The modern replacement for VLOOKUP, available in Google Sheets since 2023. XLOOKUP can search in any direction, returns a default value when no match is found, and has a cleaner syntax than INDEX-MATCH. Use XLOOKUP for new spreadsheets; use INDEX-MATCH when compatibility with older sheets is needed.

IMPORTRANGE

Syntax: =IMPORTRANGE(spreadsheet_url, range_string)

Example: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "Sheet1!A1:D100")

Use case: Pulling data from another Google Sheets file into your current workbook. Essential for businesses that maintain separate spreadsheets for different departments, months, or clients and need to consolidate data into a master dashboard.

Date and Time Functions

TODAY

Syntax: =TODAY()

Example: =IF(B2<TODAY(), "OVERDUE", "Current")

Use case: Automatically flagging overdue invoices, calculating days until a deadline, or showing the current date on a report header. TODAY updates every time the spreadsheet recalculates.

DATEDIF

Syntax: =DATEDIF(start_date, end_date, unit)

Example: =DATEDIF(B2, TODAY(), "D")

Use case: Calculating the number of days, months, or years between two dates. Use "D" for days, "M" for months, "Y" for years. Critical for invoice aging reports that categorize receivables by how many days overdue they are (current, 30, 60, 90+ days).

EOMONTH

Syntax: =EOMONTH(start_date, months)

Example: =EOMONTH(TODAY(), 0) returns the last day of the current month

Use case: Calculating payment due dates, end-of-month reporting periods, and subscription renewal dates. =EOMONTH(A2, 1) returns the last day of the month after the date in A2.

YEAR / MONTH / DAY

Syntax: =YEAR(date), =MONTH(date), =DAY(date)

Example: =YEAR(A2)&"-Q"&ROUNDUP(MONTH(A2)/3,0)

Use case: Extracting components from dates for grouping transactions by year, month, or quarter. The example formula converts any date into a "2026-Q1" format for quarterly reporting.

Text Functions

CONCATENATE / & Operator

Syntax: =A2&" "&B2 or =CONCATENATE(A2, " ", B2)

Example: ="INV-"&TEXT(ROW()-1, "000")

Use case: Generating sequential invoice numbers (INV-001, INV-002), combining first and last names, or building dynamic labels. The TEXT function within concatenation formats numbers with leading zeros or custom number formats.

TEXT

Syntax: =TEXT(value, format)

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

Use case: Formatting numbers as currency, dates as readable strings, or percentages for display in merged cells or email templates. =TEXT(A2, "MMMM YYYY") converts a date to "April 2026" format.

TRIM

Syntax: =TRIM(text)

Example: =TRIM(A2)

Use case: Cleaning imported data by removing leading, trailing, and extra internal spaces. Messy data from CSV imports often has hidden spaces that break VLOOKUP and MATCH. Always TRIM lookup values when working with imported data.

PROPER / UPPER / LOWER

Syntax: =PROPER(text)

Example: =PROPER(A2) converts "john smith" to "John Smith"

Use case: Standardizing name capitalization in client lists, invoice headers, and contact databases. UPPER converts to all caps, LOWER to all lowercase.

Financial Functions

PMT

Syntax: =PMT(rate, nper, pv)

Example: =PMT(6.5%/12, 30*12, -200000) returns $1,264.14

Use case: Calculating monthly loan or mortgage payments. Rate is the periodic interest rate (annual rate divided by 12 for monthly), nper is total number of payments, pv is the loan amount (entered as negative). Essential for our Rental Property Analyzer template.

PPMT / IPMT

Syntax: =PPMT(rate, period, nper, pv)

Example: =PPMT(6.5%/12, 1, 360, -200000) returns the principal portion of payment 1

Use case: Building amortization schedules that show how each payment splits between principal and interest. PPMT returns the principal portion and IPMT returns the interest portion for a specific payment number.

FV

Syntax: =FV(rate, nper, pmt, [pv])

Example: =FV(7%/12, 30*12, -500, -10000)

Use case: Projecting investment growth. The example calculates the future value of investing $500 per month for 30 years at 7% annual return with a $10,000 starting balance. Use for retirement planning calculators and savings goal projections.

NPV

Syntax: =NPV(rate, cashflow1, cashflow2, ...)

Example: =NPV(10%, B2:B12) + B1

Use case: Evaluating whether a business investment is worthwhile by calculating the present value of future cash flows at a given discount rate. If NPV is positive, the investment earns more than the discount rate. Used in rental property analysis and capital expenditure decisions.

IRR

Syntax: =IRR(cashflows)

Example: =IRR(B1:B10) where B1 is negative (initial investment) and B2:B10 are positive (returns)

Use case: Calculating the internal rate of return on an investment, which is the discount rate that makes NPV equal to zero. IRR allows you to compare different investments of different sizes and durations on an equal basis. Used extensively in real estate investment analysis.

Logical Functions

IF

Syntax: =IF(condition, value_if_true, value_if_false)

Example: =IF(B2>1000, B2*0.1, 0)

Use case: Applying conditional logic: charging a 10% bonus commission on sales over $1,000, flagging expenses that exceed budget, or displaying different text based on a status code. Nested IFs handle multiple conditions: =IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", "F"))).

IFS

Syntax: =IFS(condition1, value1, condition2, value2, ...)

Example: =IFS(A2>=90, "A", A2>=80, "B", A2>=70, "C", TRUE, "F")

Use case: A cleaner alternative to nested IF statements for multiple conditions. The last condition uses TRUE as a catch-all for the default value. Available in Google Sheets and Excel 365.

AND / OR

Syntax: =AND(condition1, condition2)

Example: =IF(AND(B2>0, C2="Paid"), "Complete", "Pending")

Use case: Combining multiple conditions within an IF statement. AND requires all conditions to be true; OR requires at least one. Use AND to flag invoices that are both overdue AND above a threshold amount.

IFERROR

Syntax: =IFERROR(formula, value_if_error)

Example: =IFERROR(VLOOKUP(A2, Data!A:C, 3, FALSE), "Not found")

Use case: Preventing ugly #N/A, #DIV/0!, and #REF! errors from showing in your spreadsheet. Wrap any formula that might error (especially lookups and divisions) in IFERROR to display a clean message or zero instead of an error code.

Google Sheets-Specific Functions

ARRAYFORMULA

Syntax: =ARRAYFORMULA(formula_with_ranges)

Example: =ARRAYFORMULA(IF(A2:A="", "", B2:B*C2:C))

Use case: Applying a formula to an entire column from a single cell. Instead of putting a formula in every row, one ARRAYFORMULA in row 2 handles all rows automatically. This is essential for building templates that scale as users add data. Always wrap in IF to prevent results in empty rows.

QUERY

Syntax: =QUERY(data, query, [headers])

Example: =QUERY(Transactions!A:E, "SELECT B, SUM(E) WHERE D='Marketing' GROUP BY B ORDER BY SUM(E) DESC LIMIT 10", 1)

Use case: Creating dynamic summary tables, pivot-like reports, and filtered views without pivot tables. QUERY uses Google Visualization API Query Language (similar to SQL). It can SELECT, WHERE, GROUP BY, ORDER BY, LIMIT, and PIVOT. The most powerful function unique to Google Sheets.

SPARKLINE

Syntax: =SPARKLINE(data, [options])

Example: =SPARKLINE(B2:M2, {"charttype", "column"; "color", "#4285f4"})

Use case: Creating tiny in-cell charts that show trends at a glance. Supports line, bar, column, and winloss chart types. Perfect for showing monthly spending trends, revenue growth, or budget usage in a compact dashboard format.

FILTER

Syntax: =FILTER(range, condition1, [condition2, ...])

Example: =FILTER(A2:D100, C2:C100="Overdue", D2:D100>500)

Use case: Creating dynamic filtered views that update automatically as source data changes. FILTER returns all rows matching the conditions. Use it to build an "Overdue Invoices" view, a "This Month's Expenses" list, or a filtered client list.

UNIQUE

Syntax: =UNIQUE(range)

Example: =UNIQUE(B2:B100)

Use case: Extracting a deduplicated list from a column. Use to generate a list of all unique clients, categories, or months from a transaction log. Combine with SORT for alphabetical ordering: =SORT(UNIQUE(B2:B100)).

Formula Combinations for Common Business Tasks

Monthly Expense Summary by Category

=SUMIFS(Expenses!D:D, Expenses!C:C, A2, Expenses!A:A, ">="&DATE(2026,1,1), Expenses!A:A, "<="&DATE(2026,1,31))

This formula totals expenses from a transaction log filtered by both category (in A2) and date range. Use it to build monthly expense reports across all categories.

Invoice Aging Buckets

=IFS(DATEDIF(B2,TODAY(),"D")<=30, "Current", DATEDIF(B2,TODAY(),"D")<=60, "30 Days", DATEDIF(B2,TODAY(),"D")<=90, "60 Days", TRUE, "90+ Days")

Categorizes each invoice into aging buckets based on how many days since the invoice date. Use conditional formatting to color-code each bucket.

Running Balance

=ARRAYFORMULA(IF(A2:A="", "", SUMIF(ROW(A2:A), "<="&ROW(A2:A), D2:D)))

Creates a cumulative running balance that updates automatically. Place this in a "Balance" column to show the account balance after each transaction.

Percentage of Budget Used with Visual Indicator

=IF(C2/B2>1, "OVER BUDGET", TEXT(C2/B2, "0%")&" used")

Shows budget utilization as a percentage and flags when spending exceeds the budget. Combine with conditional formatting to create a color-coded progress indicator.

Tips for Writing Better Formulas

• Use named ranges, =SUM(MonthlyExpenses) is clearer than =SUM(Sheet2!B2:B100). Named ranges also prevent breakage when rows or columns are inserted.
• Wrap lookups in IFERROR, Every VLOOKUP, INDEX-MATCH, and XLOOKUP should be wrapped in IFERROR to handle missing values gracefully.
• Use ARRAYFORMULA for scalability, Templates should use ARRAYFORMULA so formulas automatically cover new rows added by users.
• Keep formulas readable, Break complex formulas across helper columns rather than nesting 10 functions into one cell. A helper column with an intermediate calculation is easier to debug and maintain.
• Protect formula cells, In templates, protect cells containing formulas (Data > Protected sheets and ranges) so users cannot accidentally overwrite them.
• Document your formulas, Add a Notes column or use cell comments to explain what complex formulas do. Future-you will thank present-you.

Frequently Asked Questions

Related Templates

• Freelancer Financial Hub, uses SUMIFS, ARRAYFORMULA, and conditional formatting for complete financial tracking
• Small Business Bookkeeping, demonstrates QUERY, INDEX-MATCH, and VLOOKUP in a double-entry bookkeeping system
• Rental Property Analyzer, uses PMT, NPV, IRR, and FV for investment analysis
• Marketing Campaign Dashboard, uses SUMIFS, SPARKLINE, and QUERY for multi-channel analytics