Google Sheets Formulas: Practical Examples, Choosing the Right Function, and Fixing Errors

Overview

Google Sheets formulas are expressions starting with = that perform calculations or manipulate data. The most essential formulas to learn first are SUM and AVERAGE for totals, IF for rules, SUMIF and COUNTIF for summaries, and text functions for cleaning. This guide covers practical examples, selection strategies, error fixes, and maintainability patterns.

This article walks you through the formulas you’ll use most often: how to write them, when to choose one over another, what errors mean and how to fix them, and how to build formulas that survive edits and collaboration. You’ll work through a concrete example of cleaning, categorizing, and summarizing a dataset, then learn decision criteria for common formula choices and patterns that make shared sheets easier to maintain.

What Google Sheets formulas are

A formula is the full expression you type into a cell, starting with an equal sign. It can perform arithmetic, call a function, compare values, or combine multiple operations. Google Sheets supports cell formulas typically found in most desktop spreadsheet packages, and can create formulas that manipulate data and calculate strings and numbers.

Formula vs function

The distinction is simple but important. A formula is what you type in the cell: =A2+B2 or =SUM(A2:A10). A function is a built-in operation that does one job, such as SUM, IF, VLOOKUP, or FILTER. A formula can use one function (like =SUM(A1:A10)) or many (like =IF(SUM(A1:A10)>1000,"Over","Under")). When someone asks “what formula should I use?”, they often mean “which function fits my task?”

The basic syntax every formula uses

Every Google Sheets formula follows the same pattern: - Equal sign (=) — tells Google Sheets this is a formula, not text - Function name — e.g., SUM, IF, VLOOKUP - Arguments in parentheses — the inputs the function needs, separated by commas or semicolons - Cell references or ranges — e.g., A2, A1:A10 - Text in quotes — e.g., "Complete" - Operators — +, -, *, /, =, >, <, etc.

Here are common syntax patterns:

• =A1+B1 — arithmetic with cell references
• =SUM(A1:A10) — function with a range argument
• =IF(A1>100,"High","Low") — function with multiple argument types
• =VLOOKUP(A1,B1:D100,3,FALSE) — function with four arguments
• =A1&" "&B1 — concatenate (join) text using &
• =IF(AND(A1>0,B1>0),A1+B1,0) — nested functions and logic

The separator between arguments is usually a comma in English locales, but Google Sheets may use a semicolon depending on your regional settings. When in doubt, type a comma and Google Sheets will adapt.

Core Google Sheets formulas to learn first

Start with formulas that answer straightforward questions about your data: “What’s the total?” “How many rows?” “Does this meet a threshold?” These build the foundation for everything else.

Calculate totals, averages, and counts

When you need a single number summarizing a column, use aggregation functions:

• =SUM(A2:A10) — adds all values in the range
• =AVERAGE(A2:A10) — calculates the arithmetic mean
• =COUNT(A2:A10) — counts cells containing numbers
• =COUNTA(A2:A10) — counts non-empty cells (numbers, text, anything)
• =MIN(A2:A10) and =MAX(A2:A10) — find smallest and largest values

Use COUNT when you want to count only numeric entries (useful for datasets where some cells are text or blank). Use COUNTA when any filled cell counts, including text. For example, if column A contains “January”, “February”, “March”, COUNTA(A1:A3) returns 3, but COUNT(A1:A3) returns 0.

Apply rules with IF, IFS, AND, and OR

The IF function is your business-rule engine. It evaluates a condition and returns one value if true, another if false. The simplest form is =IF(condition, value_if_true, value_if_false).

Common examples: - =IF(A1>100,"Pass","Fail") — label rows based on a threshold - =IF(B1="Complete",1,0) — convert text status to a number for calculations - =IF(A1="","Missing",A1) — replace blanks with a label

For multiple conditions, nest IF or use IFS. The IFS function is cleaner when you have three or more outcomes:

=IFS(A1>=90,"A",A1>=80,"B",A1>=70,"C","F")

This grades scores: 90+ is “A”, 80–89 is “B”, 70–79 is “C”, everything else is “F”.

Use AND and OR to combine conditions. The AND function outputs TRUE if all inputs are TRUE, and FALSE otherwise; all other numbers are interpreted as TRUE by the AND function. For example:

=IF(AND(A1>0,B1>0),"Both positive","At least one is zero")

OR returns TRUE if any input is TRUE. Use it to check multiple pass-through conditions:

=IF(OR(A1="Approved",A1="Urgent"),"Process now","Queue")

Summarize categories with SUMIF, SUMIFS, COUNTIF, and COUNTIFS

When you need to add up or count only rows that match a criterion, use conditional aggregation functions. SUMIF and COUNTIF handle one criterion each; SUMIFS and COUNTIFS handle multiple criteria.

Basic syntax: - =SUMIF(range_to_check, criterion, sum_range) — sum values where a condition is met - =COUNTIF(range_to_check, criterion) — count rows matching a criterion - =SUMIFS(sum_range, criteria_range1, criterion1, criteria_range2, criterion2,...) — sum with multiple conditions - =COUNTIFS(criteria_range1, criterion1, criteria_range2, criterion2,...) — count with multiple conditions

Example: If column A holds customer names and column B holds amounts, to sum all amounts for “Acme Corp”:

=SUMIF(A:A,"Acme Corp",B:B)

For multiple conditions—sum amounts for “Acme Corp” in the “North” region—use SUMIFS:

=SUMIFS(B:B, A:A, "Acme Corp", C:C, "North")

Important note on ranges: Avoid open-ended ranges like A:A in heavily-used sheets. Specifying a bounded range (e.g., A2:A1000) is clearer and can avoid performance issues. As your data grows, update the range or use dynamic bounded formulas.

Clean and reshape text with TRIM, CLEAN, LOWER, SPLIT, JOIN, and REGEXEXTRACT

Imported or user-entered data often has inconsistencies: extra spaces, mixed case, or embedded characters. Clean it before using it in lookups or comparisons, or the formulas will fail silently.

• =TRIM(A1) — removes leading and trailing spaces
• =CLEAN(A1) — removes non-printing characters
• =LOWER(A1) — converts to lowercase; =UPPER(A1) converts to uppercase
• =SPLIT(A1," ") — splits text on a delimiter (e.g., space) across multiple columns
• =JOIN(", ",A1:A3) — joins a range of cells into one text value with a separator
• =REGEXEXTRACT(A1,"pattern") — extracts text matching a regular expression pattern

A common cleanup chain is:

=LOWER(TRIM(CLEAN(A1)))

This removes non-printing characters, trims spaces, and converts to lowercase. Use this in a helper column when text comparisons are failing mysteriously.

Work with dates without treating text as dates

Dates are tricky because they can be stored as text (from imports) or as date serials (numbers Google Sheets interprets as dates). Always verify you have a true date before using date formulas.

If you’re importing dates and they’re not auto-recognized, wrap the text in DATEVALUE():

=DATEVALUE("2026-07-09")

Once you have a true date, use these functions: - =TODAY() — returns today’s date - =DATE(year, month, day) — constructs a date from components - =YEAR(A1), =MONTH(A1), =DAY(A1) — extract parts of a date - =TEXT(A1,"YYYY-MM-DD") — formats a date as text for display or export

Be aware that locale and import format can affect how Google Sheets interprets dates. If you’re sharing a sheet with colleagues in different regions, test date formulas with sample values to ensure they work consistently.

A worked example: clean, categorize, and summarize a small dataset

Here’s a practical walkthrough using a sample dataset of transactions.

Sample sheet layout

Create a sheet with these columns:

Column	Header	Purpose
A	Date	Transaction date (text or date)
B	Customer	Customer name (may have mixed case, extra spaces)
C	Raw Category	Category label as entered (may be inconsistent)
D	Amount	Transaction amount (number)
E	Clean Category	Cleaned category output
F	Status	Rule-based status label
G	Summary Total	Lookup matching the clean category

Sample rows:

Date Customer Raw Category Amount
2026-07-05 ABC Corp office supplies 150.00
2026-07-06 ACME Corp Office Supplies 200.00
2026-07-07 xyz limited office SUPPLIES 75.00

Notice the inconsistency: “office supplies”, “Office Supplies”, “office SUPPLIES”. Before summarizing or reporting, clean it.

Step 1: clean inconsistent category text

In cell E2, enter:

=LOWER(TRIM(CLEAN(C2)))

Copy this formula down the column. It converts “office SUPPLIES” to “office supplies”, removing extra spaces and non-printing characters. The output is now consistent for lookups and grouping.

Step 2: categorize rows with a rule

In cell F2, create a status rule based on the amount:

=IF(D2>=200,"High","Standard")

Copy this down. Rows with amounts of 200 or more get “High”; others get “Standard”. This is useful for filtering or reporting by priority.

For more complex rules, use IFS:

=IFS(D2>=500,"Priority",D2>=200,"High",D2>0,"Standard",D2=0,"Zero","Invalid")

Step 3: summarize totals by category

Below your data (or in a separate area), create a summary. For each unique clean category, sum the amounts. If your clean categories are in column E and amounts in column D, use:

=SUMIF($E$2:$E$10,"office supplies",$D$2:$D$10)

This sums all amounts where the clean category is “office supplies”. The dollar signs ($) make the ranges absolute, so the formula won’t shift if you copy it.

Alternatively, use a more dynamic approach if you want to reference a lookup table. Suppose you list unique categories in column H and want the sum in column I:

=SUMIF($E$2:$E$10,H2,$D$2:$D$10)

Enter this once in I2, then copy it down. It will look up each category in H and sum matching amounts.

This worked example demonstrates the flow: import or collect raw data → clean inconsistent text → apply business rules → summarize by category. Each step uses formulas that are simple, auditable, and easy for collaborators to edit.

How to choose the right Google Sheets formula

When multiple formulas can solve the same problem, choose the one that’s simplest for your use case and easiest for teammates to understand.

Lookup formulas: VLOOKUP, XLOOKUP, and INDEX MATCH

Lookup formulas find a value in one column and return a value from another column in the same row.

• VLOOKUP(search_key, table_array, col_index_num, [is_sorted]) — searches the first column of a range and returns a value from a column to the right. Requires the lookup column to be leftmost. Matches only the first occurrence.
• XLOOKUP(search_key, search_array, return_array, [if_not_found], [match_mode], [search_mode]) — newer function that searches any column, can search left or right, and handles missing values more flexibly.
• INDEX(array, row, [column]) with MATCH(search_key, search_array, [search_type]) — more complex but powerful. Finds the position of a value with MATCH, then returns the value at that position in another column with INDEX. Works in any direction.

Choose VLOOKUP if your data is stable, the lookup column is leftmost, and you need a quick solution that most spreadsheet users will recognize. Choose XLOOKUP if you need to search right or handle missing values gracefully, and your team uses a recent version of Google Sheets. Use INDEX/MATCH if your lookup column is not leftmost or if you need very precise control.

Filtering and reporting: FILTER, QUERY, SUMIFS, and pivot tables

When you need to show a subset of rows or create a summary, you have several options:

• FILTER(range, condition1, [condition2,...]) — shows rows matching your conditions. Output is dynamic; add or remove rows from the source and the filter updates instantly.
• QUERY(data, query, [num_headers]) — uses a SQL-like language to filter, sort, and aggregate. Powerful but has a learning curve; requires understanding SQL syntax.
• SUMIFS(sum_range, criteria_range1, criterion1,...) — sums values matching multiple conditions. Simple and fast for one-off summaries.
• Pivot table — built-in Google Sheets feature (Data > Pivot table) that reorganizes data into a summary without formulas. Easy to pivot dimensions and click-friendly for non-technical users.

Use FILTER when you want a live view of matching rows and your teammates are comfortable with formula syntax. Use QUERY when you need complex sorting, grouping, or multi-column calculations; professionals need to get skilled at basic formulas like SUM, AVERAGE, IF, and COUNT then move on to lookup functions, and QUERY is the next level. Use SUMIFS for simple category summaries. Use pivot tables when you want non-technical collaborators to slice and dice without editing formulas.

Column automation: fill down vs ARRAYFORMULA

When you want a formula to apply to every row in a column, you have two approaches:

• Fill down: Enter the formula in the first cell, then drag it down or copy-paste it to all rows. Each cell has its own formula. If you add rows, you must manually extend the formula.
• ARRAYFORMULA: Wrap your formula in =ARRAYFORMULA(...) and enter it once in the first cell. It automatically applies to all rows. New rows inherit the formula instantly.

ARRAYFORMULA is elegant for large, rarely-changing datasets because it’s one formula managing the whole column. But it can be harder to debug, and applying an array formula to thousands of rows with complex calculations can slow recalculation. For small, frequently-edited sheets, explicit fill-down is often safer and easier to audit. Choose based on your team’s comfort level and sheet size.

Formulas that scale across rows, tabs, and files

Google Sheets-specific features let formulas reference data beyond a single sheet.

ARRAYFORMULA and open-ended ranges

ARRAYFORMULA lets you write one formula that applies to many rows. But be cautious with open-ended ranges like A2:A (which means “A2 to the end of the column”). In large sheets, an open-ended range can cause the formula to evaluate thousands of unnecessary rows, slowing your sheet.

Instead, use a bounded range:

=ARRAYFORMULA(IF(ROWS(A2:A1000)>0, LOWER(TRIM(A2:A1000)), ""))

This applies the cleanup formula only to rows 2 through 1000. As your data grows beyond row 1000, update the range or use a dynamic formula that adjusts based on the count of non-empty rows in a neighboring column.

Cross-tab formulas and IMPORTRANGE

To reference another sheet in the same file, use the sheet name:

=SUM(Sheet2!A2:A10)

To reference a sheet in a different file, use IMPORTRANGE:

=IMPORTRANGE("spreadsheet_url", "Sheet1!A2:A10")

The first time you use IMPORTRANGE, Google Sheets prompts for permission. After you approve, the formula fetches data from the source sheet. However, IMPORTRANGE can be fragile: if the source file’s sharing settings change, the reference breaks and returns #REF!. If the source sheet is deleted or moved, the link fails. Document all IMPORTRANGE dependencies so teammates know where data comes from and can fix breakages quickly.

Named ranges and references that survive edits

When you insert a row or column, cell references shift and formulas can break. Named ranges protect against this.

Define a named range by selecting a range (e.g., A2:A100), then going to Data > Named ranges and creating a name like customer_names. Now use:

=SUMIF(customer_names, "Acme", sales_amounts)

If you insert a row, the named range expands to include it, and your formula still works. For formulas that reference fixed structure (e.g., a lookup table that never changes), this makes maintenance much easier.

Alternatively, use absolute references with dollar signs carefully:

=VLOOKUP(A1, $B$1:$D$100, 2, FALSE)

The $B$1:$D$100 stays fixed even if you copy the formula elsewhere. The search key A1 updates when copied down.

Common Google Sheets formula errors and how to fix them

Understanding error messages helps you fix problems quickly.

#N/A, #VALUE!, #REF!, #DIV/0!, and formula parse errors

• #N/A — “Not Available.” Usually means a lookup function didn’t find a match. Check that the search value exists in the lookup column, that the data types match (text vs. number), and that you’re searching the right range. Use IFERROR or error-handling to provide a fallback.
• #VALUE! — Wrong data type for the operation. For example, if you try to add text and a number without converting, you’ll get #VALUE!. Check that your arithmetic operands are numbers, not text. Use VALUE() to convert text that looks like a number.
• #REF! — Broken reference. A formula refers to a cell or range that no longer exists (you deleted rows or columns). Edit the formula to reference the correct cells.
• #DIV/0! — Division by zero. Your formula is dividing by a cell that’s empty or contains zero. Add a check: =IF(B1=0,0,A1/B1).
• Parse error — Google Sheets can’t understand the formula syntax. Check for missing parentheses, mismatched quotes, or incorrect separators (comma vs. semicolon).

When a cell shows an error, click on it and read the error message in the formula bar. It often hints at the problem.

When IFERROR helps and when it hides problems

IFERROR(formula, value_if_error) returns a fallback value if the formula errors. It’s useful for reader-friendly output:

=IFERROR(VLOOKUP(A1,B:D,2,FALSE),"Not found")

If the lookup fails, show “Not found” instead of #N/A.

However, IFERROR can mask data quality problems. If your lookup is returning “Not found” for many rows, the issue might be bad keys, inconsistent text formatting, or a broken data pipeline upstream. Blanket IFERROR can hide these until they cause bigger problems. Use it for reader-friendly output in final reports, but during development and in shared operational sheets, let errors surface so you can investigate and fix the root cause.

Formula habits that prevent future breakage

Shared sheets are living documents. Small practices prevent formulas from drifting or breaking when others edit.

Separate raw data, helper columns, and final outputs

Organize your sheet into logical sections:

1. Raw data — imported or manually entered source data, locked or in a protected range if possible
2. Helper columns — cleanup, classification, and intermediate calculations (e.g., clean category, status labels)
3. Final outputs — summary tables, pivot tables, or reports

This separation makes it clear which columns are inputs and which are derived. If someone accidentally edits a formula in a helper column, the damage is localized. If you need to update data, you edit the raw section and formulas cascade through the rest.

Document complex formulas where collaborators will see the logic

For important formulas, add context. Use descriptive column names (instead of “F1”, use “Status_Flag”). Add a note in a nearby cell or in a comment:

Column G: Total Sales by Category
Formula uses SUMIFS to sum Sales (Column D)
where Clean Category (Column F) matches the lookup value.
Updated when raw transactions are added.

For very complex formulas, consider breaking them into separate steps (a multi-column approach) instead of nesting everything in one cell. It’s easier to audit and for collaborators to adjust.

Protect important ranges in shared sheets

In shared sheets, protect ranges containing formulas or lookup tables so accidental edits don’t break them. Go to Data > Protected ranges, select your range, and set rules (e.g., “Only me” or “Viewer and commenter only”).

Clearly separate input cells (unprotected, for data entry) from formula cells (protected, for calculations). This reduces the chance that collaborators accidentally overwrite a formula.

Google Sheets and Excel formula differences to check

If you’re switching between Google Sheets and Excel, be aware of compatibility nuances.

Function availability, separators, arrays, and imports

Not all Excel functions exist in Google Sheets, and vice versa. Before assuming a formula will port, check Google’s official function list and your version of Excel.

Argument separators differ by locale. In some regions, Google Sheets uses a semicolon (;) instead of a comma (,). Test a simple formula in your sheet; Google Sheets will auto-correct if you use the wrong separator.

Array behavior differs. Excel and Google Sheets both support array formulas, but the syntax and auto-spill behavior vary. A formula that works in one may need adjustment in the other.

Google Sheets has functions that Excel lacks, like IMPORTRANGE, QUERY, FILTER, and ARRAYFORMULA. These won’t work if you copy the sheet to Excel.

When migrating sheets between platforms, test key formulas in both environments and adjust as needed.

When formulas are not enough

Formulas are powerful, but they’re not always the best layer for every job.

Use the simplest layer that matches the job

For cell-level transformations, formulas are ideal. For quick summaries, pivot tables often beat formula-heavy sheets because they’re visual and click-friendly.

For complex automation—multi-step workflows, external data syncs, scheduled tasks—Apps Script or a database may be better than formula chains that become hard to maintain.

When your goal shifts from spreadsheet editing to data exploration and sharing, consider publishing your cleaned dataset as an interactive page. Tools like TablePage let you upload CSV, TSV, XLSX, or XLS files, instantly generating a public dataset page with filterable tables, charts, and AI insights—no signup needed. This is useful when teammates or external stakeholders need to explore your data without editing formulas, or when you want to share results without exposing your calculation logic.

The best choice depends on your team’s skills, the frequency of edits, and whether the work should live in a spreadsheet or move to a more structured layer. Start with formulas for flexibility and auditability, then move to scripts, databases, or dataset publishing tools as the task grows beyond spreadsheet scope.