Most of the time we lose in spreadsheets is not spent on hard maths. It is spent copying, counting, tidying and looking things up by hand. Five formulas cover nearly all of it, and you can learn them before your coffee goes cold.
The five
| Formula | What it does | Use it when |
|---|---|---|
SUMIF | Adds up values that match a rule | Totalling spend by category |
COUNTIF | Counts rows that match a rule | How many orders are still open? |
XLOOKUP | Finds a value in another table | Matching names to email addresses |
TRIM | Removes stray spaces | Cleaning data pasted from a website |
TEXTJOIN | Joins cells with a separator | Building a list of tags or names |
A worked example
Imagine a sheet of expenses with a category in column B and an amount in column C. To total everything in the Travel category, type this into any empty cell:
=SUMIF(B2:B200, "Travel", C2:C200)Change the word in quotes to total another category, or point it at a cell that holds the category name so you can build a whole summary table in a minute.
Learn the formula once, then let the spreadsheet do the boring part forever.
Lesson 3, Data Basics
Try each formula on a copy of a real sheet from your own work. That is the fastest way to make it stick, and it is exactly how the Data Basics course is built.
