Google Sheets Formulas Cheat Sheet

Master Google Sheets with our comprehensive formulas reference guide. From basic math to powerful Google-exclusive functions like QUERY, ARRAYFORMULA, and IMPORTRANGE — find syntax, examples, and expert tips for every essential formula.

Found 9 formulas matching "SUM"

SUMIF()

Math & Statistical

Sums cells that meet a single criterion

Syntax:

=SUMIF(range, criterion, [sum_range])

Examples:

=SUMIF(A1:A10, ">100")

Sum values greater than 100

=SUMIF(A1:A10, "Apple", B1:B10)

Sum B values where A contains 'Apple'

=SUMIF(A1:A10, A12, B1:B10)

Sum B where A equals A12 value

Note: If sum_range is omitted, the range itself is summed

SUMIFS()

Math & Statistical

Sums cells that meet multiple criteria

Syntax:

=SUMIFS(sum_range, criteria_range1, criterion1, ...)

Examples:

=SUMIFS(C1:C10, A1:A10, "Apple", B1:B10, ">100")

Sum C where A='Apple' AND B>100

=SUMIFS(D1:D10, B1:B10, "North", C1:C10, "Q1")

Sum D for North region in Q1

=SUMIFS(C:C, A:A, "*Pro*", B:B, ">0")

Sum C where A contains 'Pro' and B>0

Note: All criteria must be met simultaneously for a cell to be included

DSUM()

Database

Sums values in a column of a database that match specified criteria

Syntax:

=DSUM(database, field, criteria)

Examples:

=DSUM(A1:D100, "Sales", F1:F2)

Sum Sales column where F criteria match

=DSUM(A:D, 3, F1:F2)

Sum 3rd column matching criteria

Note: database includes headers; criteria range must also have matching headers

SUM()

Math & Statistical

Adds all numbers in a range

Syntax:

=SUM(value1, [value2], ...)

Examples:

=SUM(A1:A10)

Sum all values in range A1 to A10

=SUM(A1, B1, C1)

Sum specific cells

=SUM(A1:A5, C1:C5)

Sum multiple ranges

Note: Ignores text values and empty cells

OFFSET()

Lookup & Reference

Returns a range offset from a starting cell by rows and columns

Syntax:

=OFFSET(cell_reference, rows, cols, [height], [width])

Examples:

=OFFSET(A1, 2, 1)

Cell 2 rows down, 1 column right from A1

=SUM(OFFSET(A1, 0, 0, 5, 1))

Sum 5 rows starting at A1

=OFFSET(A1, COUNTA(A:A)-1, 0)

Last non-empty cell in column A

Note: Creates a dynamic reference; use with care as it is volatile (recalculates often)

INDIRECT()

Lookup & Reference

Returns a reference specified by a text string

Syntax:

=INDIRECT(cell_reference_as_text)

Examples:

=INDIRECT("A"&B1)

Reference cell A[value in B1]

=SUM(INDIRECT(A1&":"&B1))

Sum dynamic range from text

=INDIRECT("Sheet2!A1")

Reference another sheet dynamically

Note: Volatile function — use sparingly; useful for dynamic range references

ARRAYFORMULA()

Google-Specific ⭐

Enables a formula to return multiple results across a range

Syntax:

=ARRAYFORMULA(array_formula)

Examples:

=ARRAYFORMULA(A1:A10*B1:B10)

Multiply columns element-wise

=ARRAYFORMULA(IF(A2:A100="","",A2:A100*1.2))

Apply formula to entire column

=ARRAYFORMULA(SUM(IF(A1:A10>5,A1:A10,0)))

Conditional sum with array logic

Note: ⭐ Google Sheets exclusive; shortcut Ctrl+Shift+Enter auto-wraps with ARRAYFORMULA

QUERY()

Google-Specific ⭐

Runs a SQL-like query on your data using Google Visualization API query language

Syntax:

=QUERY(data, query, [headers])

Examples:

=QUERY(A1:D100, "SELECT A, B WHERE C > 100")

Select columns where C>100

=QUERY(A:D, "SELECT A, SUM(D) GROUP BY A ORDER BY SUM(D) DESC")

Sum by group, sorted

=QUERY(A:C, "SELECT * WHERE B = '" & E1 & "'")

Dynamic filter using cell value

Note: ⭐ Google Sheets exclusive; one of the most powerful functions for data analysis

DAVERAGE()

Database

Averages values in a column that match specified criteria

Syntax:

=DAVERAGE(database, field, criteria)

Examples:

=DAVERAGE(A1:D100, "Score", F1:F2)

Average Score where criteria match

Note: Same structure as DSUM; criteria uses a separate range with headers

Google Sheets Mastery Guide

Beginner

Start with calculations and basic data organisation

Intermediate

Master lookups and Google-specific power features

Advanced

QUERY language, regex, and live data imports

📊 What is Google Sheets?

Google Sheets is a free, cloud-based spreadsheet application from Google, available as part of Google Workspace. First launched in 2006, it has grown into a powerful alternative to Microsoft Excel — and in several areas surpasses it, with unique features like real-time collaboration, built-in QUERY language, live data imports, and formulas for translating text and fetching stock prices directly in a cell.

🚀 Core Capabilities

  • ✓ Real-time collaboration: Multiple users edit simultaneously
  • ✓ Cloud storage: Auto-saves to Google Drive
  • ✓ QUERY formula: SQL-like data analysis in a cell
  • ✓ Live data imports: IMPORTHTML, IMPORTXML, GOOGLEFINANCE
  • ✓ ARRAYFORMULA: Apply formulas to entire columns instantly
  • ✓ Apps Script: JavaScript-based automation and macros

💼 Google Sheets Exclusive Functions

  • ⭐ QUERY: SQL-like language for filtering and aggregating data
  • ⭐ ARRAYFORMULA: Array operations without Ctrl+Shift+Enter
  • ⭐ IMPORTRANGE: Pull data live from another spreadsheet
  • ⭐ GOOGLETRANSLATE: Translate text between 100+ languages
  • ⭐ GOOGLEFINANCE: Live stock prices and financial data
  • ⭐ SPARKLINE: Mini charts directly inside a cell

⚡ Google Sheets vs. Excel

Google Sheets Advantages

  • • Free to use with a Google account
  • • Real-time multi-user collaboration
  • • QUERY, ARRAYFORMULA, FILTER, UNIQUE built-in
  • • Live web data imports (IMPORTHTML, IMPORTXML)
  • • Version history with per-cell tracking
  • • Works in any browser, no install needed

Excel Advantages

  • • More powerful PivotTables and data model
  • • Better performance with very large datasets
  • • VBA macros for complex automation
  • • More chart types and formatting options
  • • Power Query for ETL operations
  • • Industry standard in finance and enterprise

Google Sheets Pro Tips

ARRAYFORMULA shortcut: Press Ctrl+Shift+Enter in a cell to automatically wrap your formula with ARRAYFORMULA — applies it to the entire column instantly.

QUERY SQL-like syntax: Use SELECT, WHERE, GROUP BY, ORDER BY, LIMIT just like SQL — e.g. QUERY(A:D, "SELECT A, SUM(D) GROUP BY A")

IMPORTRANGE cross-sheet tip: Paste the full Google Sheets URL as the first argument. You only need to grant access once — after that, data syncs live between spreadsheets automatically.

FILTER with OR logic: Use + between conditions (not OR()) — e.g. FILTER(A:B, (A:A="X")+(B:B="Y"))

Master Google Sheets formulas with practice. Bookmark this page for quick reference! ⭐ marks Google-exclusive functions.

📊 99 Total Formulas 🏷️ 10 Categories ⭐ Google-Exclusive Functions