Google Sheets formulas for data analysis: the 10 you actually need

most people use 5% of Google Sheets’ formula capability and do the other 95% manually.

this guide covers the formulas that eliminate the most manual work for solopreneurs doing business data analysis — with real examples for revenue tracking, customer data, and reporting.

SUMIF — total values matching a condition

syntax: =SUMIF(range, criteria, sum_range)

what it does: adds up values in one column where a corresponding column matches your condition.

business example: total revenue from “North America” region.
=SUMIF(B:B, "North America", E:E)

adds up all values in column E where column B = “North America.”

SUMIFS for multiple conditions:
=SUMIFS(E:E, B:B, "North America", C:C, "Enterprise")

total revenue from North America AND Enterprise tier customers.

COUNTIF — count rows matching a condition

syntax: =COUNTIF(range, criteria)

business example: how many orders came from “Australia”?
=COUNTIF(B:B, "Australia")

COUNTIFS for multiple conditions:
=COUNTIFS(B:B, "Australia", D:D, "2026")

count orders from Australia in 2026.

AVERAGEIF — average for rows matching a condition

syntax: =AVERAGEIF(range, criteria, average_range)

business example: average order value for the Enterprise tier.
=AVERAGEIF(C:C, "Enterprise", E:E)

VLOOKUP — find a value in another table

syntax: =VLOOKUP(lookup_value, table_range, column_number, FALSE)

what it does: searches the first column of a range for your value and returns the corresponding value from another column.

business example: you have an order table with Product IDs and a product table with Product ID + Name. to add the product name to each order:
=VLOOKUP(A2, ProductTable, 2, FALSE)

searches ProductTable for the value in A2, returns column 2 (the product name).

important: always use FALSE as the last argument for exact match. TRUE gives approximate match and produces wrong results in most business contexts.

XLOOKUP (modern replacement):
=XLOOKUP(A2, ProductTable[ID], ProductTable[Name], "Not Found")

XLOOKUP is more flexible: searches in any column (not just the first), handles the “not found” case cleanly, and is not broken by inserting new columns in the lookup table.

INDEX MATCH — more flexible than VLOOKUP

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

use INDEX MATCH when you need to look up a value based on a column that is not the first column of your table.

business example: you have a table with Customer Name in column B, ID in column C, and Revenue in column D. find the revenue for customer “Acme Corp”:
=INDEX(D:D, MATCH("Acme Corp", B:B, 0))

QUERY — SQL-style queries inside Google Sheets

syntax: =QUERY(data_range, "SELECT A, B WHERE C > 1000 ORDER BY D DESC")

QUERY is the most powerful Google Sheets formula. it lets you write SQL-like queries against any range.

filter and sort:
=QUERY(A1:G1000, "SELECT A, B, E WHERE C = 'North America' ORDER BY E DESC")

returns columns A, B, E where column C = “North America”, sorted by column E descending.

aggregate with GROUP BY:
=QUERY(A1:G1000, "SELECT B, SUM(E) GROUP BY B LABEL SUM(E) 'Total Revenue'")

revenue by region — same as a pivot table, but refreshes dynamically.

date filters:
=QUERY(A1:G1000, "SELECT A, E WHERE D >= DATE '2026-01-01'")

QUERY is the formula to learn if you want to replace manual filtering and pivot tables with formulas that update automatically.

ARRAYFORMULA — apply a formula to the whole column at once

syntax: =ARRAYFORMULA(formula)

without ARRAYFORMULA, a formula in B2 calculates for row 2 only. you copy it down to B3, B4, B5… which breaks when new rows are added above.

ARRAYFORMULA applies the formula to the entire column in one cell:
=ARRAYFORMULA(IF(A2:A<>"", A2:A * 1.1, ""))

multiplies every non-blank value in column A by 1.1, across all rows, from a single formula in B2.

use for calculated columns: instead of dragging a formula down 1,000 rows, one ARRAYFORMULA in the header row handles all current and future rows.

FILTER — dynamic filtered views

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

FILTER returns a subset of rows from a range matching your conditions. unlike hiding rows manually, the output updates automatically when the source data changes.

business example: show only orders over $1,000 from column A-E:
=FILTER(A1:E1000, E1:E1000>1000)

multiple conditions:
=FILTER(A1:E1000, E1:E1000>1000, C1:C1000="Enterprise")

FILTER is the formula equivalent of slicing a pivot table — you get a clean, automatically updating view of a subset without modifying the source data.

IMPORTRANGE — pull data from another Google Sheet

syntax: =IMPORTRANGE("spreadsheet_url", "Sheet1!A1:E1000")

IMPORTRANGE connects two Sheets. pull data from your team’s master data sheet into your analysis sheet without copy-pasting.

use case: a central data collection Sheet (where the team enters data) and a separate analysis Sheet (your dashboard). IMPORTRANGE keeps them synchronized without manual exports.

UNIQUE and SORT — deduplicate and rank lists

UNIQUE: returns the unique values from a column.
=UNIQUE(B2:B1000) — returns a deduplicated list of regions.

SORT: sorts a range dynamically.
=SORT(UNIQUE(B2:B1000), 1, TRUE) — sorted unique list of regions.

combine with COUNTIF to get a distribution table:

A: =SORT(UNIQUE(B2:B1000))
B: =ARRAYFORMULA(COUNTIF(B2:B1000, A2:A100))

this gives you a count of each unique category, sorted — in two formulas, without a pivot table.

combining formulas for real analysis

revenue and order count by region (without a pivot table):

A1: =SORT(UNIQUE(Data!C:C))           — unique regions
B1: =ARRAYFORMULA(SUMIF(Data!C:C, A1:A20, Data!E:E))  — revenue
C1: =ARRAYFORMULA(COUNTIF(Data!C:C, A1:A20))           — order count
D1: =ARRAYFORMULA(B1:B20/C1:C20)     — average order value

this four-formula block creates a dynamic region summary that updates automatically as new orders are added to the Data sheet.

for more on building dashboards from this kind of formula output: how to build a business dashboard without code.

for the pivot table approach: Google Sheets pivot table tutorial for beginners.

for comparison with Excel’s formula ecosystem: Excel vs Google Sheets for data analysis 2026.