Most people use Google Sheets as a grid for typing numbers and add things up by hand. A handful of formulas can do the counting, matching and summarizing for you, and they keep working as the data grows. Here are eight worth learning, each with an example you can paste into your own sheet.
For the examples, imagine a sheet with a header row and five columns: A is Date, B is Region, C is Rep, D is Sales, and E is Target (a sales goal for that row), with data starting in row 2.
1. SUMIFS: add up only what matches
=SUMIFS(D2:D, B2:B, "West", C2:C, "Ana")
SUMIFS adds up a range based on several conditions at once. This one totals the Sales column only for rows where the Region is West and the Rep is Ana. Add more pairs of criteria range and criterion to narrow it further.
2. COUNTIFS: count rows that meet conditions
=COUNTIFS(B2:B, "West", D2:D, ">500")
COUNTIFS counts the entries that satisfy multiple conditions. This counts West-region sales over 500. It is the quickest way to answer “how many” questions without filtering and eyeballing.
3. IFERROR: hide errors you expect
=IFERROR(D2/E2, "")
Dividing Sales by Target gives an error whenever a Target is empty or zero, and failed lookups throw errors too. IFERROR returns your value unless it’s an error, in which case it returns the alternative you give it (here, an empty cell). Use it where an error is expected and harmless, not to hide mistakes you haven’t investigated.
4. XLOOKUP: find a value and return something next to it
=XLOOKUP("Ana", C2:C, D2:D, "Not found")
XLOOKUP searches one column or row for a key and returns the matching value from another. Its main advantages over VLOOKUP: the result column can sit to the left of the search column, you don’t count column numbers, and the “not found” message is built in. If you omit that fourth argument and nothing matches, you’ll get #N/A. Google’s own documentation notes the lookup range and result range should be the same size.
For comparison, the older VLOOKUP equivalent needs the search column first and a column index: =VLOOKUP("Ana", C2:D, 2, FALSE).
5. FILTER: pull out only the rows you want
=FILTER(A2:D, B2:B="West", D2:D>500)
FILTER returns the rows of a range that meet your conditions, and it updates when the source data changes. This is the formula to reach for when you’d otherwise copy and paste filtered results into a second tab. Each condition range must be the same height as the data range, or Sheets will return an error.
6. UNIQUE and SORT: clean lists in one line
=SORT(UNIQUE(FILTER(B2:B, B2:B<>"")), 1, TRUE)
UNIQUE removes duplicate rows and keeps the order they first appear. The FILTER inside removes the empty cells below your data, so the list doesn’t end with a blank entry. Wrapping it in SORT gives you an alphabetical list of every region that appears in your data, with no copying and deduplicating by hand. It is also a handy source for a dropdown list.
7. QUERY: a mini database inside your sheet
=QUERY(A1:D, "select B, sum(D) where D > 0 group by B order by sum(D) desc", 1)
QUERY runs a database-style query on your data using the Google Visualization API Query Language. The last argument, 1, tells it there is one header row. This example totals sales per region and sorts the biggest first, which would otherwise take a pivot table or several SUMIFS formulas. Each column in the range should hold one kind of value (all numbers, all text, or all dates), or QUERY may treat the odd ones out as blanks.
8. ARRAYFORMULA: one formula for a whole column
=ARRAYFORMULA(IF(D2:D="", "", D2:D * 1.1))
Normally you write a formula in one cell and drag it down. ARRAYFORMULA lets a single formula fill the entire column, and it also covers new rows as they are added. Here the IF keeps blank rows blank while every filled row gets Sales plus 10 percent. Put it in the first data row of an otherwise empty column.
Mistakes that quietly break formulas
- Numbers stored as text. If a total looks too low, check whether the cells are left-aligned text rather than numbers.
- Ranges that don’t line up. FILTER conditions and XLOOKUP ranges need matching sizes.
- Forgetting to lock references. When you copy a formula that points at a fixed cell, such as a tax rate in F1, write it as
$F$1so the reference doesn’t shift. - Typing the data inside the formula. Put values like “West” or 500 in cells and reference them, so you can change them without editing formulas.
Where to start
If you only learn three, make them SUMIFS, XLOOKUP, and QUERY. Together they cover most of the totals, lookups and summaries people build by hand. And if you also keep notes next to your data, our comparison of Notion vs Google Docs for note-taking covers where each fits.
Sources:
- XLOOKUP function (Google Docs Editors Help) — https://support.google.com/docs/answer/12405947
- QUERY function (Google Docs Editors Help) — https://support.google.com/docs/answer/3093343
- FILTER function (Google Docs Editors Help) — https://support.google.com/docs/answer/3093197
- Google Sheets function list (Google Docs Editors Help) — https://support.google.com/docs/table/25273
