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 "COUNT"

COUNT()

Math & Statistical

Counts the number of numeric values in a range

Syntax:

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

Examples:

=COUNT(A1:A10)

Count numeric cells in range

=COUNT(A:A)

Count numeric cells in entire column

=COUNT(A1, B1, C1)

Count specific numeric cells

Note: Only counts cells with numeric values; use COUNTA for non-empty cells

COUNTIF()

Math & Statistical

Counts cells that meet a single criterion

Syntax:

=COUNTIF(range, criterion)

Examples:

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

Count cells greater than 100

=COUNTIF(A1:A10, "Apple")

Count cells equal to 'Apple'

=COUNTIF(A1:A10, "<>"&"")

Count non-empty cells

Note: Use wildcards (* ?) for partial matching

DCOUNT()

Database

Counts numeric cells in a column that match specified criteria

Syntax:

=DCOUNT(database, field, criteria)

Examples:

=DCOUNT(A1:D100, "Amount", F1:F2)

Count numeric Amount values matching criteria

Note: Use DCOUNTA to count non-empty cells (including text)

LEN()

Text

Returns the number of characters in a string

Syntax:

=LEN(text)

Examples:

=LEN(A1)

Character count of A1

=LEN(TRIM(A1))

Length after trimming whitespace

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

Count commas in a string

Note: Includes spaces and special characters in the count

NPV()

Financial

Calculates net present value of an investment with periodic cash flows

Syntax:

=NPV(discount, cashflow1, [cashflow2], ...)

Examples:

=NPV(0.1, B2:B7)-A1

NPV of future cash flows minus initial investment

=NPV(0.08, 1000, 2000, 3000)

NPV of three yearly cash flows at 8%

Note: First payment occurs at end of period 1; subtract initial investment separately

ISBLANK()

Information

Returns TRUE if the cell is empty

Syntax:

=ISBLANK(value)

Examples:

=ISBLANK(A1)

TRUE if A1 is empty

=IF(ISBLANK(A1), "No data", A1)

Show 'No data' for empty cells

=COUNTIF(A1:A10, "")

Count blanks (alternative approach)

Note: A cell with a formula returning empty string is not considered blank

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)

DMIN()

Database

Returns the minimum value in a column that matches specified criteria

Syntax:

=DMIN(database, field, criteria)

Examples:

=DMIN(A1:D100, "Cost", F1:F2)

Minimum Cost for matching rows

Note: Counterpart to DMAX

XOR()

Logical

Returns TRUE if an odd number of conditions are true

Syntax:

=XOR(logical1, [logical2], ...)

Examples:

=XOR(A1>0, B1>0)

TRUE if exactly one of A1,B1 is positive

=XOR(TRUE, FALSE)

Returns TRUE

=XOR(TRUE, TRUE)

Returns FALSE (both true = even count)

Note: Useful for exclusive-or logic where exactly one condition should be true

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