What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
The most useful Google Sheets formulas depend on the job: SUM totals numbers, IF applies a rule, XLOOKUP retrieves a matching value, FILTER extracts records, SORT reorders them, and QUERY filters or summarizes a table. ARRAYFORMULA, LET, BYROW, and MAP reduce repetitive work across ranges.
Each example below assumes column A contains a name or product, B contains a category or identifier, and C contains a numeric value. Replace those ranges with the columns in your own sheet. Google Sheets formulas begin with =; function names and argument separators can vary with your spreadsheet’s locale settings.
Start with a safe formula setup
Make a copy of the sheet, or test on a representative section before changing a production workbook. Check a few results manually against the source rows. That catches shifted ranges, text stored as numbers, duplicate keys, and accidental references to headers.
- Put headers in a separate first row and decide whether a function should include them.
- Keep ranges aligned. A range such as
A2:A100has 99 rows, so a condition range used with it should cover the same rows. - Use absolute references such as
$A$2:$A$100when copying a formula and the lookup range must not move. - If your locale uses semicolons instead of commas, replace the separators shown in these examples. Google Sheets also lets you change the function language in spreadsheet settings.
- Leave room for formulas that return multiple cells. An occupied destination cell causes a spill or expansion error.
Which formula fits the task?
| Need | Best starting point | Result shape | Important check |
|---|---|---|---|
| Add a numeric range | SUM |
One value | Numbers must be recognized as numeric values. |
| Choose between two outcomes | IF |
One value per formula row | Write the condition and both outcomes explicitly. |
| Replace an error with a controlled result | IFERROR |
One value | Do not hide a data problem with a misleading zero. |
| Find a key and return its related value | XLOOKUP |
Usually one value | Decide what should happen when no key matches. |
| Extract rows meeting conditions | FILTER |
Spilled range | Condition ranges must have matching dimensions. |
| Order a table | SORT |
Spilled range | Use the correct relative sort-column number. |
| Filter, group, or aggregate a table | QUERY |
One or many cells | Keep each data column consistently typed and set the header count. |
| Calculate down a range | ARRAYFORMULA |
Spilled range | Check whether the expression already expands automatically. |
| Name and reuse an intermediate result | LET |
One formula result | Each value expression is evaluated once. |
| Apply a custom calculation to rows or paired values | BYROW or MAP |
One result per row or element | The LAMBDA arguments and returned shape must match the function. |
1. SUM: total a range
Use SUM for a straightforward total, such as revenue, hours, quantities, or expenses.
=SUM(C2:C100)The range can be a column segment, a row, or several references. For example, =SUM(C2:C100,E2:E100) adds two non-adjacent ranges. SUM ignores text in the referenced cells, so values that look like numbers but are stored as text may be left out. If a total seems too low, inspect the cell format and the underlying value rather than adding a second formula that masks the issue.
2. IF: return a result based on a condition
IF expresses a two-way rule: return one value when a test is true and another when it is false.
=IF(C2>=100,"Review","OK")Here, amounts of 100 or more receive the label “Review”; smaller amounts receive “OK.” You can test text, dates, checkboxes, or several conditions combined with logical functions. Keep the output labels precise, because downstream filters and pivot-style summaries treat them as data. To avoid filling a label on blank rows, add a blank check first, for example =IF(A2="","",IF(C2>=100,"Review","OK")).
3. IFERROR: provide a deliberate fallback
IFERROR returns the expression’s value when it works and a fallback when the expression produces an error.
=IFERROR(A2/B2,0)This prevents a divide-by-zero or missing-reference error from appearing in a report. Zero is not always an honest fallback: it can imply that a measured amount is actually zero. Use a blank, an explanatory label, or a separate review flag when the error indicates incomplete data, such as =IFERROR(A2/B2,"Check denominator"). Apply IFERROR after you understand the underlying error; otherwise it can conceal misspelled ranges, unexpected text, or a broken import.
4. XLOOKUP: return the value aligned with a key
Use XLOOKUP when a key in one range identifies a related value in another range. In this example, E2 contains the key to find in column A, and the corresponding value comes from column C.
Rank #2
- hole punched
- high quality card stock
- 4 pages
- made in USA
- keyboard shortcuts
=XLOOKUP(E2,A2:A100,C2:C100,"Not found")The fourth argument supplies a readable result when there is no match. The function reference also supports match-mode and search-mode arguments, which are useful when you need an exact, approximate, wildcard, or reverse search. Keep the lookup and result ranges the same height. If keys are duplicated, decide whether the first or last occurrence is meaningful and choose the search behavior accordingly; otherwise the returned record may not be the one a colleague expects.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstall5. FILTER: extract only matching records
FILTER creates a live view of rows that meet one or more conditions. This example returns columns A through C for North records with a positive amount.
=FILTER(A2:C100,B2:B100="North",C2:C100>0)Every condition must line up with the filtered range: the conditions above are row-oriented and each covers rows 2 through 100. Do not combine a vertical condition with a horizontal one. Multiple conditions are treated together, so a row must satisfy both tests. If no row matches, FILTER returns #N/A. Handle that outcome intentionally, for example with =IFNA(FILTER(A2:C100,B2:B100="North"),"No matching records"), when a no-results message is preferable to an error. Leave the destination area empty so the returned table can expand.
6. SORT: reorder a range without changing the source
SORT returns a sorted copy of a range, keeping the original data in place. The following formula sorts A2:C100 by its third column, C, in descending order.
=SORT(A2:C100,3,FALSE)The second argument is the sort-column position within the supplied range, not necessarily the worksheet’s column number. TRUE sorts ascending and FALSE sorts descending. You can add further sort-column and direction pairs when ties need a deterministic order. Because the result spills into cells, place it on a report area rather than over the source table or other content.
Free tools Windows power users keep installed
One-click scans. No signup required.
7. QUERY: filter, group, and summarize tabular data
QUERY applies a query expression to a range. It is useful when a report needs selected columns, conditions, grouping, and aggregation in one result.
=QUERY(A1:C100,"select A, sum(C) where A is not null group by A",1)The final 1 tells Sheets that the first row of the supplied range contains one header row. Change that number when your actual range has a different header count. The query text uses a SQL-like language, so quote text criteria and keep column letters tied to the range you pass in.
Each QUERY data column should be consistently typed as boolean, numeric (including date and time), or string. Mixed types in one column create a constraint and can cause values to be treated unexpectedly. Clean imported data before querying, and verify that dates are real date values rather than date-looking text. QUERY can replace a chain of FILTER and SORT operations when the report is fundamentally a table selection or summary, but its query syntax requires more care than a simple condition.
8. ARRAYFORMULA: calculate across many cells at once
ARRAYFORMULA lets one expression produce results for a range instead of copying a formula row by row.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →=ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C))This example multiplies the corresponding values in columns B and C for every nonblank row in A. The blank test prevents an entire column of zeros or errors below the active data. Some functions already return arrays that expand into neighboring cells automatically, so do not wrap every multi-cell expression in ARRAYFORMULA as a ritual. Also note that Google Sheets array formulas cannot be exported; if another system requires individual stored values, convert the results to values or use an export method that supports the output.
9. LET: name an intermediate result
LET assigns a name to an intermediate value and then reuses it inside the same formula. That makes repeated logic easier to read and avoids recalculating the same expression.
=LET(total,SUM(C2:C100),IF(total>1000,"Target met","In progress"))Here, total stores the SUM result and the IF statement refers to that name. Names must be valid identifiers and are local to the formula. LET evaluates each value expression once, which is useful when the expression is long or expensive. Give names that describe their meaning—such as total, rate, or latestDate—so someone maintaining the workbook can understand the formula without decoding repeated ranges.
Rank #4
- Mastering Google Sheets: A Step by Step Handbook for Beginners to Simplify Data Analysis, Boost Productivity, and Unlock Your Full Spreadsheet Potential
- ABIS BOOK
10. BYROW or MAP: apply a LAMBDA calculation across data
Use BYROW when the calculation should run once for each row of a two-dimensional range. The LAMBDA must return one scalar value per row.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minute=BYROW(C2:E100,LAMBDA(row,SUM(row)))The result is one sum for every row in C2:E100. A LAMBDA that returns multiple cells for one row violates BYROW’s required shape.
Use MAP when the calculation should run once for each corresponding element in one or more arrays. MAP requires one LAMBDA parameter for each input array.
=MAP(B2:B100,C2:C100,LAMBDA(category,amount,IF(category="North",amount,0)))This returns the amount for North rows and zero for other categories. The two input ranges must align element by element. Choose BYROW for row-level summaries, such as totals across several month columns; choose MAP for paired or parallel columns, such as applying a category rule to each amount.
How to choose between similar methods
| Situation | Prefer | Why | Watch for |
|---|---|---|---|
| One value from a known key | XLOOKUP | It states the lookup range, result range, and missing-value behavior directly. | Duplicate keys and mismatched range lengths. |
| A live list of rows matching simple conditions | FILTER | The conditions are visible beside the source range. | Aligned dimensions, #N/A for no matches, and spill space. |
| A grouped or aggregated report | QUERY | Selection, grouping, and functions such as sum can be expressed together. |
Header count and mixed data types. |
| A calculation repeated across rows | BYROW | The LAMBDA receives one complete row at a time. | It must return one scalar per row. |
| A calculation using corresponding columns | MAP | Each LAMBDA call receives paired values from the input arrays. | One parameter per array and equal-length inputs. |
| A formula that needs a reusable subtotal or threshold | LET | Names make nested logic easier to maintain. | Keep names clear and valid. |
Troubleshoot before blaming the formula
- Unexpected zero or blank: check whether a number is stored as text, whether IFERROR is masking an error, or whether SUM is pointing at the wrong rows.
#N/Afrom FILTER: confirm that at least one record satisfies every condition, then decide whether to show a no-results message.- QUERY gives odd totals: inspect each column for mixed text and numeric values, and verify the header-count argument.
- Spill or expansion error: clear cells in the formula’s expected output area. A formula returning many cells cannot overwrite existing content.
- Lookup misses an obvious key: compare data types, trim accidental spaces, and confirm that the lookup and result ranges cover the same records.
- BYROW or MAP rejects the LAMBDA: make sure BYROW returns one value per row and MAP has exactly one parameter for each supplied array.
- Formula will not parse: try your locale’s argument separator and confirm the function language setting.
A practical way to adopt the ten formulas
- Classify the task as aggregation, decision, lookup, extraction, sorting, summarization, or repeated calculation.
- Build the smallest working version on a copied range, including an explicit no-match or error outcome where appropriate.
- Check the output against several manually verified rows, including a blank, a boundary value, a duplicate key, and a no-match case when those situations can occur.
- Move a spilling formula to a clear report area and label its output so colleagues know it is generated.
- Once the result is correct, improve readability with LET or a named range instead of making a single formula longer without structure.
These patterns cover the routine work that otherwise leads to repeated copy-and-paste: add with SUM, classify with IF, protect known error paths with IFERROR, retrieve with XLOOKUP, extract with FILTER, order with SORT, summarize with QUERY, fill ranges with ARRAYFORMULA, organize complex logic with LET, and apply custom functions with BYROW or MAP.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
