# Excel Formulas That Do Real Work > The Excel formulas people use at work every day: XLOOKUP and its older cousins, IF and IFS, SUMIFS and COUNTIFS, text cleanup, and dates, all on one small orders-and-products dataset, plus the classic mistakes that quietly give wrong answers. --- # Excel Formulas That Do Real Work You can write `=SUM(B2:B20)` and you know what a cell reference is. Then a real task shows up: pull each product's price into the orders sheet, total sales for one region in one month, clean up a column of names that someone typed with stray spaces. The formulas for those jobs exist, but the wrong one fails quietly, and a wrong number that looks fine is worse than an error. This guide teaches the formulas that carry most real spreadsheet work, and the specific ways each one goes wrong. Every example uses one small dataset you can type in yourself, so you can check each result against your own screen. Written for Excel for Microsoft 365. XLOOKUP, IFS, and TEXTJOIN need a recent version (IFS and TEXTJOIN: Excel 2019 or newer; XLOOKUP: Excel 2021 or newer), and TEXTSPLIT needs Microsoft 365 or Excel 2024. Older functions are covered too, because you will meet them in files other people built. ## Prerequisite You should know what a cell reference is, how to fill a formula down, and `SUM`. If not, start with [Excel From Zero](/guides/excel-from-zero). ## The running dataset Three small sheets. Type them in as you go (the values are all you need; formatting is optional). **Products** sheet, `A1:D7`: | SKU | Product | Category | Unit Price | |---|---|---|---| | P100 | Desk Lamp | Lighting | 40 | | P200 | Office Chair | Furniture | 120 | | P300 | Standing Desk | Furniture | 300 | | P400 | Monitor Arm | Accessories | 55 | | P500 | Keyboard | Accessories | 70 | | P600 | Webcam | Accessories | 90 | **Orders** sheet, `A1:F9` (columns G and H get built with formulas in phase 1): | OrderID | OrderDate | Customer | SKU | Qty | Region | |---|---|---|---|---|---| | 1001 | 2026-01-05 | Nora Patel | P200 | 2 | East | | 1002 | 2026-01-12 | Ben Ortiz | P100 | 5 | West | | 1003 | 2026-01-30 | Nora Patel | P300 | 1 | East | | 1004 | 2026-02-03 | Chloe Dang | P500 | 3 | West | | 1005 | 2026-02-14 | Ben Ortiz | P200 | 1 | West | | 1006 | 2026-02-20 | Dev Rao | P400 | 4 | East | | 1007 | 2026-03-02 | Chloe Dang | P300 | 2 | West | | 1008 | 2026-03-09 | Nora Patel | P600 | 1 | East | Enter the dates as real dates: type `2026-01-05` and Excel converts it in most locales. Phase 5 shows how to check. **Tiers** sheet, `A1:B4` (a quantity discount table): | Min Qty | Discount | |---|---| | 0 | 0% | | 3 | 5% | | 5 | 10% | ## How to read this - **Need a lookup right now?** Phase 1. - **Getting a wrong number or a stray `#N/A`?** Jump to the cheat table at the end of [Phase 5](05-dates-and-the-classic-mistakes.md). - **Want it to finally make sense?** Read in order. Each phase reuses the same data. ## The phases 1. **[Lookups: XLOOKUP, INDEX/MATCH, and VLOOKUP](01-lookups.md)** - pulling a value from another table, and why old VLOOKUP files break. 2. **[Logic: IF, IFS, AND, OR, and IFERROR](02-logic-and-iferror.md)** - making a cell decide, and when error-hiding hides a real bug. 3. **[Conditional Totals: SUMIFS, COUNTIFS, AVERAGEIFS](03-conditional-totals.md)** - totals by region, month, or customer. 4. **[Text: Cleaning, Splitting, and Joining](04-text.md)** - TRIM, LEFT, MID, TEXTJOIN, TEXT, and numbers stored as text. 5. **[Dates and the Classic Mistakes](05-dates-and-the-classic-mistakes.md)** - serial numbers, EDATE, EOMONTH, NETWORKDAYS, DATEDIF, and a symptom-to-fix table. Lookups are joins under another name; if you know [SQL joins](/guides/sql-joins-explained), phase 1 will feel familiar. Pivot tables, charts, and the deeper tools live in [Excel Pivot Tables and Charts](/guides/excel-pivot-tables-and-charts) and [Advanced Excel](/guides/advanced-excel). When a workbook outgrows formulas, see [Power BI From Zero](/guides/power-bi-from-zero). --- # Lookups: XLOOKUP, INDEX/MATCH, and VLOOKUP Your Orders sheet has a SKU like `P200` and a quantity. The price lives on a different sheet. You need Excel to find `P200` in the Products table and bring back its price. That job is a **lookup**, and it is the same idea as a join in SQL: match a key in one table against a key in another and fetch a column. ## The mental model: find the row, then fetch from it Every lookup does two things. It finds the **position** of the key in a column, then returns the value at that same position in another column. The three functions below differ only in how you spell those two steps. ## XLOOKUP ```excel =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) ``` Only the first three arguments are required. In Orders, click `G2` and type: ```excel =XLOOKUP(D2, Products!$A$2:$A$7, Products!$D$2:$D$7) ``` Read it as: "find `D2` (`P200`) in the SKU column, and return the Unit Price from the same row." The result is `120`. In `H2` type `=E2*G2`, which gives `240`. Fill both down. | D2 (SKU) | Found at | Returned | |---|---|---| | P200 | 2nd row of the table | 120 | | P100 | 1st row | 40 | | P600 | 6th row | 90 | The defaults matter, and Microsoft documents them: - `match_mode` defaults to `0`, an **exact** match. If nothing matches, you get `#N/A`. - `search_mode` defaults to `1`, searching from the first item down. - `if_not_found` defaults to `#N/A`; give it a value to show something friendlier. ```excel =XLOOKUP("P999", Products!$A$2:$A$7, Products!$D$2:$D$7, "Not in catalog") ``` returns the text `Not in catalog`. Leave the fourth argument off and the same formula returns `#N/A`. ### The other modes `match_mode` has four values: `0` exact, `-1` exact or the next smaller item, `1` exact or the next larger item, and `2` wildcard match using `*`, `?`, and `~`. `search_mode` has four: `1` first to last, `-1` last to first, `2` binary search ascending, and `-2` binary search descending. The binary modes need sorted data; skip them until you have a large sorted table. A real use of `-1` is a tier table. The Tiers sheet says 0 units earns 0%, 3 units earns 5%, and 5 units earns 10%. For a quantity in `E2`: ```excel =XLOOKUP(E2, Tiers!$A$2:$A$4, Tiers!$B$2:$B$4, , -1) ``` The empty fourth argument keeps the default. Quantity `4` is not in the table, so `-1` returns the next smaller entry, `3`, giving `5%`. | Qty | Tier row used | Discount | |---|---|---| | 2 | 0 | 0% | | 3 | 3 (exact) | 5% | | 4 | 3 (next smaller) | 5% | | 5 | 5 (exact) | 10% | Wildcards plus a reverse search find the last match: ```excel =XLOOKUP("*Desk*", Products!$B$2:$B$7, Products!$A$2:$A$7, , 2, -1) ``` Two products contain "Desk" (Desk Lamp and Standing Desk). Searching last to first returns `P300`; with the default direction it would return `P100`. XLOOKUP needs Excel 2021 or Microsoft 365. It is not in Excel 2016 or 2019. ## INDEX and MATCH Older files split the two steps into two functions. - `MATCH(lookup_value, lookup_array, [match_type])` returns the **position** of the item, not the item. `match_type` defaults to `1` (largest value less than or equal, which requires ascending data). Use `0` for exact. - `INDEX(array, row_num, [column_num])` returns the value at a position. For a single column, `row_num` alone is enough. ```excel =INDEX(Products!$D$2:$D$7, MATCH(D2, Products!$A$2:$A$7, 0)) ``` For `P200`, `MATCH` returns `2` and `INDEX` returns the 2nd price, `120`. Same answer as XLOOKUP. Write the `0` every time: leave it off and you get the approximate default. Both functions can look in any direction. To get a SKU from a product name, `=INDEX(Products!$A$2:$A$7, MATCH("Keyboard", Products!$B$2:$B$7, 0))` returns `P500` (the position is `5`). ## VLOOKUP ```excel =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) ``` It searches the **first column** of `table_array` and returns a value from column number `col_index_num`, counting from 1. ```excel =VLOOKUP(D2, Products!$A$2:$D$7, 4, FALSE) ``` returns `120`. VLOOKUP breaks in three ways, and you will see all of them in old files. **1. The default is approximate.** If you omit the fourth argument, `range_lookup` is `TRUE`, which assumes the first column is sorted and returns the closest value that is not larger. Your table is sorted, so look at what a missing SKU does: ```excel =VLOOKUP("P250", Products!$A$2:$D$7, 4) ``` There is no `P250`, yet you get `120` (the `P200` price) with no error. A wrong answer that looks right. Microsoft's own note: with `TRUE`, an unsorted first column gives unexpected results. Always write `FALSE` (or `0`) for an exact match. **2. The column number is a hard-coded count.** The `4` means "fourth column of the range". Change which columns the range covers, or insert a column inside the table, and the `4` can silently point somewhere else. Ask for a column beyond the range (say `5` here) and you get `#REF!`. XLOOKUP and INDEX/MATCH name the return column directly, so they do not have this problem. **3. It only looks to the right.** The key must be in the first column of the range. To look left you need INDEX/MATCH or XLOOKUP. ## The fill-down trap: absolute references Why do the formulas above write `Products!$A$2:$A$7`? The `$` signs lock the range. Suppose you typed `Products!A2:A7` and filled down. In `G3` Excel shifts the range one row, to `Products!A3:A8`. `D3` holds `P100`, which lives in `A2`, now outside the range, so the result is `#N/A`. The window keeps sliding down as you fill, so the first row looks fine and any later row whose SKU sits above the window fails. Rule: the **lookup value** moves with each row (relative: `D2`); the **table** stays put (absolute: `$A$2:$A$7`). Press `F4` while the cursor is in a reference to cycle through the `$` forms. ## Your turn: predict the result ```exercise [ { "type": "predict", "task": "Using the Products table, what does =XLOOKUP(\"P250\", Products!$A$2:$A$7, Products!$D$2:$D$7, , -1) return? Give the number.", "accept": ["120"], "hint": "Match mode -1 returns the exact match or the next SMALLER item when there is no exact match. Which SKU sits immediately below P250?" }, { "type": "predict", "task": "Same lookup, but the match mode is 1 instead of -1 (exact or next LARGER item). What price does it return?", "accept": ["300"], "hint": "The next larger SKU after P250 is P300." } ] ``` Check yourself before moving on: ```quiz [ {"q": "A colleague's old workbook uses VLOOKUP with no fourth argument and returns a price for a SKU that is not in the table. What is the most likely cause?", "choices": ["The omitted range_lookup defaults to TRUE, an approximate match", "VLOOKUP always ignores the first column", "The column number is too large", "The table has a hidden row"], "answer": 0, "explain": "When range_lookup is omitted it is TRUE, so VLOOKUP returns the closest value that is not larger instead of reporting #N/A. Write FALSE for an exact match.", "why": [null, "VLOOKUP searches the first column; that is how it works.", "A column number beyond the range gives #REF!, not a plausible price.", "Hidden rows do not change what a lookup finds."]}, {"q": "You fill XLOOKUP down a column and the first row works but many later rows return #N/A. What is the likely fix?", "choices": ["Change match_mode to 2", "Use VLOOKUP instead", "Lock the lookup_array and return_array with dollar signs", "Sort the Orders sheet"], "answer": 2, "explain": "Relative ranges shift down with each row and slide off the table. Absolute references like $A$2:$A$7 keep the table fixed.", "why": ["Wildcard matching does not fix a shifted range.", "VLOOKUP would have the same relative-range problem.", null, "Sorting the orders does not move the lookup table."]}, {"q": "Which XLOOKUP default is true according to Microsoft's documentation?", "choices": ["if_not_found defaults to 0", "match_mode defaults to 0, an exact match", "search_mode defaults to binary search", "match_mode defaults to approximate match"], "answer": 1, "explain": "XLOOKUP is exact by default (match_mode 0), searches first to last (search_mode 1), and returns #N/A when nothing is found unless you set if_not_found."} ] ``` ## Recap 1. A lookup finds a key's position in one column and returns the value at that position from another. 2. `XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])` is exact by default and needs Excel 2021 or Microsoft 365. 3. `INDEX(range, MATCH(key, range, 0))` does the same in two steps and works in every version; always write the `0`. 4. VLOOKUP defaults to approximate match and uses a hard-coded column number; write `FALSE` and treat the number with suspicion. 5. Lock the table with `$` when you fill a lookup down. Next up, [Phase 2: Logic](02-logic-and-iferror.md): making a cell decide, and when hiding errors hides bugs. --- # Logic: IF, IFS, AND, OR, and IFERROR Soon after lookups, spreadsheets need decisions: flag the big orders, label the tiers, show a message instead of an error. The functions are short. The skill is in the order you write the tests, and in knowing that the most popular helper, `IFERROR`, can hide a bug for months. Keep the Orders sheet from phase 1: column `G` is Unit Price, `H` is Total (`=E2*G2`). The totals for orders 1001 to 1008 are `240, 200, 300, 210, 120, 220, 600, 90`. ## IF: one yes/no decision ```excel =IF(logical_test, value_if_true, value_if_false) ``` Label an order "Bulk" when the quantity is 5 or more: ```excel =IF(E2>=5, "Bulk", "Regular") ``` Only order 1002 (quantity 5) says `Bulk`; the other seven say `Regular`. Text results go in quotes. Comparisons with `=` ignore upper and lower case, so `F2="east"` is true for `East`. ## IFS: several tests in order Nesting `IF` inside `IF` gets unreadable. `IFS` (Excel 2019 and newer) takes pairs of test and result: ```excel =IFS(H2>=500, "Large", H2>=200, "Medium", TRUE, "Small") ``` Excel checks the tests **from left to right and returns the result of the first one that is true**. The final `TRUE, "Small"` is the standard way to write a default. Without it, a row that matches nothing returns `#N/A`. | OrderID | Total | Result | |---|---|---| | 1001 | 240 | Medium | | 1002 | 200 | Medium | | 1005 | 120 | Small | | 1007 | 600 | Large | Because the first true test wins, **order matters**. Swap the first two tests: ```excel =IFS(H2>=200, "Medium", H2>=500, "Large", TRUE, "Small") ``` Now order 1007 (`600`) is `Medium`, because `600>=200` is true and Excel never reaches the `500` test. Write the strictest test first. ## AND and OR `AND` is true only when **every** test is true. `OR` is true when **at least one** is. Each returns `TRUE` or `FALSE`, so they usually live inside an `IF`. ```excel =IF(AND(F2="East", H2>=250), "Review", "OK") ``` Flags East orders worth 250 or more. Of the four East orders (1001: 240, 1003: 300, 1006: 220, 1008: 90), only 1003 says `Review`. Order 1007 is worth 600 but is West, so it says `OK`. ```excel =IF(OR(E2>=5, H2>=500), "Priority", "Normal") ``` Order 1002 qualifies by quantity and order 1007 by total; both say `Priority`, and the other six say `Normal`. ## IFERROR: handle the error, or hide the bug ```excel =IFERROR(value, value_if_error) ``` If `value` evaluates to an error, you get `value_if_error`; otherwise you get `value`. It catches every error type: `#N/A`, `#VALUE!`, `#REF!`, `#DIV/0!`, `#NUM!`, `#NAME?`, and `#NULL!`. That last sentence is the problem. `#N/A` from a lookup that found nothing is an expected situation. `#REF!` from a broken column number is a mistake in your formula. IFERROR treats them the same and shows your fallback for both. Here is a real trap. Someone writes the price lookup this way: ```excel =IFERROR(VLOOKUP(D2, Products!$A$2:$D$7, 5, FALSE), 0) ``` The range has only 4 columns, so the `5` makes VLOOKUP return `#REF!` on every row. IFERROR swallows it. Every price becomes `0`, every order total becomes `0`, and the sheet shows no error at all. A bare formula would have shown `#REF!` in the first cell and you would have fixed it in seconds. Safer habits: - **Handle only the expected case.** For a lookup, use XLOOKUP's built-in `if_not_found`, which fires only when the key is missing: `=XLOOKUP(D2, Products!$A$2:$A$7, Products!$D$2:$D$7, "CHECK SKU")`. - **Use `IFNA(value, value_if_na)`** when you need to wrap an older lookup. It replaces only `#N/A` and lets every other error through, so a broken column number still shouts. - **Make the fallback visible.** A fallback of `0` blends into real numbers; text like `"CHECK SKU"` stands out and breaks later math loudly instead of quietly. - **Prefer an explicit guard for known edge cases.** For a possible blank quantity: `=IF(E2="", "", H2/E2)` instead of wrapping the division in IFERROR. Use IFERROR when you truly mean "any failure here is fine", for example a cosmetic display cell nothing else depends on. For anything feeding totals, aim at the one error you expect. ## Your turn: predict the result ```exercise [ { "type": "predict", "task": "Order 1007 has a total of 600. What does =IFS(H2>=200, \"Medium\", H2>=500, \"Large\", TRUE, \"Small\") return for it? Answer with the single word.", "accept": ["Medium"], "hint": "IFS returns the result of the FIRST true test, reading left to right. Is 600 >= 200?" }, { "type": "predict", "task": "Order 1005 is West, quantity 1, total 120. What does =IF(OR(E2>=5, H2>=500), \"Priority\", \"Normal\") return? Answer with the single word.", "accept": ["Normal"], "hint": "OR needs at least one true test. Is 1 >= 5? Is 120 >= 500?" } ] ``` Check yourself before moving on: ```quiz [ {"q": "A price lookup is wrapped as IFERROR(VLOOKUP(...), 0) and a typo in the column number makes it return #REF! on every row. What does the sheet show?", "choices": ["A circular reference warning", "Only the first row is wrong", "#REF! in every row", "0 in every row, with no error visible"], "answer": 3, "explain": "IFERROR catches every error type, including #REF!, so the real bug is replaced by the fallback value 0.", "why": ["Nothing in the formula refers to its own cell.", "The mistake affects every row the same way.", "That is what you would see without IFERROR.", null]}, {"q": "Which choice replaces only #N/A and lets other errors through?", "choices": ["IFERROR", "IFNA", "IFS", "AND"], "answer": 1, "explain": "IFNA(value, value_if_na) only handles #N/A. IFERROR handles all error types."}, {"q": "In an IFS formula the tests are H2>=200 then H2>=500. A total of 600 returns the result for which test?", "choices": ["Both results, joined together", "An error, because the tests overlap", "The second test, because 600 is larger", "The first test, because IFS returns the first true one"], "answer": 3, "explain": "IFS stops at the first true test. Put the strictest condition first."} ] ``` ## Recap 1. `IF(test, if_true, if_false)` decides one thing; `IFS` takes test and result pairs and returns the first true one, so strictest test first and `TRUE` as the default. 2. `AND` needs all tests true; `OR` needs one. 3. `IFERROR` catches every error type, so it can turn a broken formula into a plausible-looking `0`. 4. Prefer XLOOKUP's `if_not_found`, `IFNA`, or an explicit `IF` guard, and make fallbacks visible. Next up, [Phase 3: Conditional Totals](03-conditional-totals.md): adding up only the rows that match. --- # Conditional Totals: SUMIFS, COUNTIFS, AVERAGEIFS "What were East sales in February?" is the most common question a spreadsheet gets. You could filter the table and read the status bar, but the answer goes stale the moment the data changes. The `-IFS` family answers it with a formula that recalculates itself. The idea: each function takes pairs of **a range to test and a condition**. A row counts only if it passes **every** pair. Use the Orders sheet with Total in `H2:H9`, Region in `F2:F9`, Customer in `C2:C9`, Quantity in `E2:E9`, and OrderDate in `B2:B9`. ## SUMIFS ```excel =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) ``` The first argument is the thing to add up. Then come the pairs. ```excel =SUMIFS(H2:H9, F2:F9, "East") ``` East orders are 1001 (240), 1003 (300), 1006 (220), and 1008 (90): `240 + 300 + 220 + 90 = 850`. The West total is `200 + 210 + 120 + 600 = 1130`, and `850 + 1130 = 1980`, the sum of the whole column. Add a second pair to narrow further: ```excel =SUMIFS(H2:H9, F2:F9, "East", C2:C9, "Nora Patel") ``` East orders by Nora Patel are 1001, 1003, and 1008: `240 + 300 + 90 = 630`. ### The argument-order trap The older single-condition function is `SUMIF(range, criteria, [sum_range])`. Its sum range is **third and optional**. In SUMIFS the sum range is **first and required**. Microsoft calls this out as a common source of problems. Compare: ```excel =SUMIF(F2:F9, "East", H2:H9) =SUMIFS(H2:H9, F2:F9, "East") ``` Both return `850`. Mix up the order and you get zero or an error, not a helpful message. You can use SUMIFS for everything and ignore SUMIF, but you will read SUMIF in old files, so know both. ### Criteria: operators, dates, and cell references A criterion is text. Plain values match exactly (`"East"`). To compare, put the operator inside the quotes: `">=200"`, `"<>East"`, `"<5"`. To compare against a value in a cell or a function result, **join the operator to it with `&`**: ```excel =SUMIFS(H2:H9, B2:B9, ">="&DATE(2026,2,1), B2:B9, "<"&DATE(2026,3,1)) ``` This is the standard "everything in February" pattern: on or after Feb 1 and before Mar 1. Orders 1004, 1005, and 1006 qualify: `210 + 120 + 220 = 550`. Using "before the first of next month" avoids having to know whether the month ends on the 28th, 30th, or 31st. Wildcards work in text criteria: `*` for any run of characters, `?` for one character, `~` to match a literal `*` or `?`. ```excel =COUNTIFS(C2:C9, "N*") ``` counts customers whose name starts with N: Nora Patel appears three times, so `3`. ### Fill-down and the summary grid A common layout is a small summary table: regions in `J2:J3`, totals next to them. ```excel =SUMIFS($H$2:$H$9, $F$2:$F$9, $J2) ``` The ranges are locked with `$` so they stay put as you fill down; `$J2` has a locked column and a free row, so it moves down to `J3` for the next region. The criteria can be a cell reference, which is cleaner than typing `"East"` into every formula. ## COUNTIFS and AVERAGEIFS ```excel =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...) =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) ``` COUNTIFS has no value range: it counts rows. AVERAGEIFS starts with the range to average, like SUMIFS. | Formula | Reads as | Result | |---|---|---| | `=COUNTIFS(H2:H9, ">=200")` | orders worth 200 or more | 6 | | `=COUNTIFS(F2:F9, "West", E2:E9, ">=3")` | West orders of 3+ units | 2 | | `=AVERAGEIFS(H2:H9, F2:F9, "West")` | average West order | 282.5 | Check the first: totals of 240, 200, 300, 210, 220, and 600 reach 200; 120 and 90 do not, so `6`. The second: West orders 1002 (qty 5) and 1004 (qty 3) pass, while 1005 (qty 1) and 1007 (qty 2) do not, so `2`. The third: `1130 / 4 = 282.5`. If no row matches, AVERAGEIFS has nothing to divide, and returns `#DIV/0!`. For example `=AVERAGEIFS(H2:H9, F2:F9, "North")` is `#DIV/0!` because there is no North. That error carries real information: "nothing matched". Resist wrapping it blindly (see phase 2). ## Why they return the wrong number - **Range sizes must match.** Every range in the formula must have the same number of rows and columns. `SUMIFS(H2:H9, F2:F8, "East")` returns `#VALUE!`. - **Text criteria need quotes.** `"East"`, not `East`. - **Invisible differences.** A customer stored as `"Nora Patel "` with a trailing space does not match `"Nora Patel"`. Phase 4 shows how to find and clean these. - **Numbers stored as text** in the criteria range or sum range are the other classic cause of totals that are too low. See phase 4. - **TRUE and FALSE in a sum range** count as 1 and 0, which can add surprise values. ## Your turn: predict the result ```exercise [ { "type": "predict", "task": "What does =SUMIFS(H2:H9, F2:F9, \"West\", B2:B9, \">=\"&DATE(2026,2,1)) return? (West orders on or after Feb 1 are 1004, 1005, and 1007; their totals are 210, 120, and 600.)", "accept": ["930"], "hint": "Add the three totals: 210 + 120 + 600." }, { "type": "predict", "task": "What does =COUNTIFS(C2:C9, \"Nora Patel\", F2:F9, \"East\") return?", "accept": ["3"], "hint": "Count the rows that pass BOTH tests. Nora's orders are 1001, 1003, and 1008, all East." } ] ``` Check yourself before moving on: ```quiz [ {"q": "Which formula correctly totals column H where column F is East?", "choices": ["=SUMIFS(F2:F9, H2:H9, \"East\")", "=SUMIFS(F2:F9, \"East\", H2:H9)", "=SUMIFS(H2:H9, F2:F9, \"East\")", "=SUMIFS(\"East\", F2:F9, H2:H9)"], "answer": 2, "explain": "In SUMIFS the sum range comes first, then each criteria range followed by its criterion. In the older SUMIF the sum range is third.", "why": ["The pair order is range then criterion, and the sum range is first.", "That puts the criteria range first and a criterion in the pair position.", null, "The sum range must be a range and come first."]}, {"q": "You want everything dated in February 2026. Which criteria pair is the safest pattern?", "choices": ["On or after DATE(2026,2,1) and before DATE(2026,3,1)", "Between 28 and 31", "Text criteria of Feb", "Dates equal to February"], "answer": 0, "explain": "Joining the operator to DATE() with & and using 'before the first of next month' works whatever the month length is."}, {"q": "AVERAGEIFS returns #DIV/0! for region North. What does that most likely mean?", "choices": ["Excel ran out of memory", "The formula has a typo in its syntax", "No row matched the criteria, so there was nothing to average", "The ranges are different sizes"], "answer": 2, "explain": "Microsoft documents that AVERAGEIFS returns #DIV/0! when no cells meet the criteria. Mismatched range sizes give #VALUE! instead.", "why": ["This error is about division by zero rows.", "A syntax problem would show a different error or refuse the formula.", null, "Different sizes produce #VALUE!."]} ] ``` ## Recap 1. The `-IFS` functions take range and criterion pairs; a row counts only if it passes every pair. 2. `SUMIFS(sum_range, ...)` puts the sum range first; `SUMIF(range, criteria, [sum_range])` puts it third. 3. Join operators to values with `&`: `">="&DATE(2026,2,1)`. Use "on or after the 1st, before the 1st of next month" for a month. 4. All ranges must be the same size, and stray spaces or text-numbers make rows silently miss. 5. `AVERAGEIFS` returns `#DIV/0!` when nothing matches. Next up, [Phase 4: Text](04-text.md): cleaning and reshaping the text those criteria depend on. --- # Text: Cleaning, Splitting, and Joining Data that comes from other people and other systems is rarely clean. Names have extra spaces, IDs arrive glued together, and numbers show up as text that looks like numbers. None of that is visible on screen, but every one of them makes a lookup return `#N/A` or a total come out too small. This phase gives you the tools to see it and fix it. ## The mental model: text is a string of characters A cell value is either a **number**, a **date** (a number with a format, see phase 5), or **text**. Text is a sequence of characters counted from 1, spaces included. `LEFT`, `MID`, and `RIGHT` take slices of that sequence. ## Measuring and cleaning: LEN and TRIM `LEN(text)` returns the number of characters. `TRIM(text)` removes all spaces from text except single spaces between words. Suppose `A2` holds a name someone typed carelessly: two spaces before, three between, one after (underscores stand for spaces in the table): | Cell | Contents | Formula | Result | |---|---|---|---| | A2 | `__Nora___Patel_` | `=LEN(A2)` | 15 | | B2 | | `=TRIM(A2)` | `Nora Patel` | | C2 | | `=LEN(B2)` | 10 | The raw cell has 2 + 4 + 3 + 5 + 1 = 15 characters; the clean one has `4 + 1 + 5 = 10`. Comparing `LEN` before and after is the fastest way to catch a hidden space. **This is why a lookup fails.** `=XLOOKUP("P200 ", Products!$A$2:$A$7, Products!$D$2:$D$7)` has a space after the key and returns `#N/A`, because `"P200 "` and `"P200"` are different strings. Cure it at the source: `=XLOOKUP(TRIM(D2), ...)`, or clean the column once with `TRIM` and paste the values. One limit, documented by Microsoft: `TRIM` removes the ordinary space (character code 32) but **not** the non-breaking space (code 160) that often comes from web pages. Convert it first: ```excel =TRIM(SUBSTITUTE(A2, CHAR(160), " ")) ``` ## Slicing: LEFT, RIGHT, MID, FIND ```excel =LEFT(text, [num_chars]) =RIGHT(text, [num_chars]) =MID(text, start_num, num_chars) ``` Say `A2` holds an order reference `WEST-1002-P100` (14 characters: `WEST`, a hyphen, `1002`, a hyphen, `P100`). | Formula | Result | Why | |---|---|---| | `=LEFT(A2, 4)` | `WEST` | the first 4 characters | | `=RIGHT(A2, 4)` | `P100` | the last 4 characters | | `=MID(A2, 6, 4)` | `1002` | 4 characters starting at position 6 | | `=FIND("-", A2)` | `5` | position of the first hyphen | | `=MID(A2, FIND("-", A2) + 1, 4)` | `1002` | start one past the hyphen | `FIND` makes the slice survive references of different lengths, because it locates the delimiter instead of hard-coding a position. (`FIND` is case-sensitive; `SEARCH` is the case-insensitive version.) ### Splitting in one step: TEXTSPLIT In Microsoft 365 and Excel 2024: ```excel =TEXTSPLIT(A2, "-") ``` spills the pieces across three cells: `WEST`, `1002`, `P100`. Its full form is `TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])`. In older versions use `LEFT`, `MID`, `FIND`, or the Data > Text to Columns tool. ## Joining: TEXTJOIN and & `&` joins two things: `=C2&" "&D2`. For many pieces with a separator, use `TEXTJOIN` (Excel 2019 and newer): ```excel =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...) ``` `ignore_empty` is required: `TRUE` skips empty cells, `FALSE` includes them. With first name `Nora` in `A2`, an empty middle name in `B2`, and last name `Patel` in `C2`: | Formula | Result | |---|---| | `=TEXTJOIN(" ", TRUE, A2:C2)` | `Nora Patel` | | `=TEXTJOIN(" ", FALSE, A2:C2)` | `Nora Patel` (two spaces) | Because it accepts ranges, it can list things: `=TEXTJOIN(", ", TRUE, Products!B2:B4)` gives `Desk Lamp, Office Chair, Standing Desk`. ## Formatting for display: TEXT `TEXT(value, format_text)` turns a number or date into text formatted a given way. It is for building labels: ```excel ="Order "&A2&" total "&TEXT(H2, "$#,##0") ``` For order 1001 (total 240) this gives `Order 1001 total $240`. Other codes: `TEXT(DATE(2026,1,5), "yyyy-mm-dd")` gives `2026-01-05`, `TEXT(DATE(2026,1,5), "mmm yyyy")` gives `Jan 2026`, and `TEXT(0.05, "0%")` gives `5%`. Two cautions. The result is **text**, so you cannot sum it; Microsoft recommends keeping the original number in its own cell. And format codes follow regional settings (the thousands separator varies, and some languages use different letters for year or day), so a workbook shared across countries can behave differently. ## Numbers stored as text A cell can show `1002` and still be text. Signs: it is left-aligned, a small green triangle appears, `=ISNUMBER(cell)` returns `FALSE`. The usual sources are imported files, leading apostrophes, and the slices above: `MID`, `LEFT`, and `RIGHT` always return **text**, even when it looks like a number. That matters for lookups. Orders `A2:A9` hold real numbers (`1001` to `1008`): ```excel =XLOOKUP(MID(A2, 6, 4), Orders!$A$2:$A$9, Orders!$C$2:$C$9) ``` with `A2` of a scratch sheet holding `WEST-1002-P100` looks for the text `"1002"` among numbers, and returns `#N/A`. Convert it with `VALUE`: ```excel =XLOOKUP(VALUE(MID(A2, 6, 4)), Orders!$A$2:$A$9, Orders!$C$2:$C$9) ``` This returns `Ben Ortiz` (order 1002). Other ways to convert: multiply by 1, use Data > Text to Columns, or re-enter the data. Text-numbers also hurt totals: `SUM` ignores text in a range, so a column with a few text-numbers adds up to less than it should with no warning. ## Your turn: predict the result ```exercise [ { "type": "predict", "task": "A2 holds WEST-1002-P100. What does =RIGHT(A2, 4) return?", "accept": ["P100"], "hint": "RIGHT takes characters from the end of the text." }, { "type": "predict", "task": "A2 holds WEST-1002-P100. What does =LEN(A2) return? Give the number.", "accept": ["14"], "hint": "Count WEST (4), the hyphen (1), 1002 (4), the hyphen (1), and P100 (4)." } ] ``` Check yourself before moving on: ```quiz [ {"q": "XLOOKUP returns #N/A for a key that visibly matches a value in the table. Which is a likely cause?", "choices": ["The lookup table is too short", "A trailing space or a number stored as text makes the two values different", "XLOOKUP cannot look up text", "The result column is formatted as currency"], "answer": 1, "explain": "Exact lookups compare the characters. P200 with a trailing space is a different string from P200, and the text 1002 does not equal the number 1002.", "why": ["Length of the table does not matter if the key is in it.", null, "XLOOKUP works with text and numbers.", "Formatting of the result cell does not affect matching."]}, {"q": "MID(A2, 6, 4) returns 1002 from a reference code. What type is that result?", "choices": ["A date", "It depends on the cell format", "A number", "Text"], "answer": 3, "explain": "LEFT, MID, and RIGHT always return text. Wrap the result in VALUE to get a number."}, {"q": "TRIM does not clean a cell that came from a web page. What is the likely reason?", "choices": ["TRIM only works on numbers", "The cell contains non-breaking spaces (character 160), which TRIM does not remove", "TRIM removes only the leading spaces", "TRIM needs a second argument"], "answer": 1, "explain": "Microsoft notes TRIM removes the ordinary space character (code 32) but not the non-breaking space (code 160). Replace it with SUBSTITUTE(A2, CHAR(160), \" \") first.", "why": ["TRIM works on text.", null, "It also removes extra trailing and between-word spaces.", "TRIM takes only one argument."]} ] ``` ## Recap 1. Text is a numbered sequence of characters; `LEN` counts them and exposes hidden spaces. 2. `TRIM` removes extra ordinary spaces; use `SUBSTITUTE(x, CHAR(160), " ")` for non-breaking ones. 3. `LEFT`, `RIGHT`, `MID`, and `FIND` slice by position; `TEXTSPLIT` splits by delimiter in Microsoft 365. 4. `TEXTJOIN(delimiter, ignore_empty, ...)` joins with a separator; `TEXT` formats numbers and dates into text for labels. 5. Slices are text. Convert with `VALUE`, or lookups and totals will quietly miss. Next up, [Phase 5: Dates and the Classic Mistakes](05-dates-and-the-classic-mistakes.md): how Excel stores dates, and a symptom-to-fix table. --- # Dates and the Classic Mistakes Dates look like text and behave like numbers, which is why they are both convenient to calculate with and fragile to break. Once you know what is under the hood, "30 days from the order date" is one subtraction. This phase covers the date functions you actually use, then closes with a table of the mistakes from the whole guide, organized by symptom. ## Dates are serial numbers Excel stores a date as a count of days. By default, January 1, 1900 is serial number `1`. Microsoft's reference gives a second anchor: January 1, 2008 is `39448`, which is 39,447 days after January 1, 1900. Our order dates follow the same counting: | Date | Serial number | |---|---| | 2026-01-05 | 46027 | | 2026-01-30 | 46052 | | 2026-03-09 | 46090 | The only difference between a date and a number is the **cell format**. Type `46027` in a cell, choose a date format, and it shows `2026-01-05`; choose General and a real date shows `46027`. Because dates are numbers, subtraction gives a duration in days: ```excel =B9-B2 ``` Order 1008 (2026-03-09) minus order 1001 (2026-01-05) is `46090 - 46027 = 63` days. Adding works too: `=B2+30` gives the date 30 days after order 1001, `2026-02-04`. (Format the result cell as a date or you will see a plain number.) **Is it a real date?** `=ISNUMBER(B2)` is `TRUE` for a real date. If a date is left-aligned, or `ISNUMBER` says `FALSE`, it is text, and no date function will work on it. Convert it with `DATEVALUE`, retype it, or rebuild it with `DATE(year, month, day)`. Prefer `DATE` in formulas: a typed text date like `03/04/2026` can mean March 4 or April 3 depending on regional settings, while `DATE(2026,3,4)` cannot. ## TODAY `=TODAY()` returns the current date. It recalculates whenever the workbook recalculates, so the same formula gives a different answer tomorrow. That is the point for "days since the order" (`=TODAY()-B2`), and a hazard when you need a number to stay fixed in a report you are saving: paste the value instead. ## EDATE and EOMONTH: month arithmetic Adding 30 days is not the same as adding one month. Month functions handle the varying lengths for you. ```excel =EDATE(start_date, months) =EOMONTH(start_date, months) ``` Both return a serial number (format the cell as a date). `months` can be negative to go back. - `EDATE` returns the same day-of-month the given number of months away. - `EOMONTH` returns the **last day** of the month that is that many months away. `0` means the same month. | Order | Order date | Formula | Result | |---|---|---|---| | 1001 | 2026-01-05 | `=EDATE(B2, 1)` | 2026-02-05 | | 1003 | 2026-01-30 | `=EDATE(B4, 1)` | 2026-02-28 | | 1003 | 2026-01-30 | `=EOMONTH(B4, 0)` | 2026-01-31 | | 1003 | 2026-01-30 | `=EOMONTH(B4, -1) + 1` | 2026-01-01 | February has no 30th, so `EDATE` lands on the last day, February 28. `EOMONTH(date, -1) + 1` is the standard way to get the **first** day of a month: the end of last month, plus one day. Putting `=EOMONTH(B2, 0)` in a helper column gives every order a month key you can group and `SUMIFS` on. ## NETWORKDAYS ```excel =NETWORKDAYS(start_date, end_date, [holidays]) ``` Counts whole working days (Monday to Friday) between two dates, **including both ends**, minus any dates in the optional `holidays` range. Microsoft recommends building dates with `DATE`, because text dates cause problems. ```excel =NETWORKDAYS(DATE(2026,1,5), DATE(2026,1,16)) ``` January 5, 2026 is a Monday and January 16 a Friday: two full work weeks, so `10`. If `E1` holds a holiday of `2026-01-12`, then `=NETWORKDAYS(DATE(2026,1,5), DATE(2026,1,16), E1)` returns `9`. Different weekend days need `NETWORKDAYS.INTL`. ## DATEDIF, with caveats ```excel =DATEDIF(start_date, end_date, unit) ``` `unit` is one of `"Y"` (complete years), `"M"` (complete months), `"D"` (days), `"MD"`, `"YM"`, `"YD"`. It was kept for compatibility with older spreadsheet programs, and Microsoft warns it can give incorrect results in some scenarios and advises against the `"MD"` unit because of known limitations. From a customer's start date of 2025-03-15 to 2026-01-05: | Formula | Result | Why | |---|---|---| | `=DATEDIF(DATE(2025,3,15), DATE(2026,1,5), "Y")` | 0 | not yet a full year | | `=DATEDIF(DATE(2025,3,15), DATE(2026,1,5), "M")` | 9 | 15 March to 15 December is 9 months; 5 January is before the 15th | | `=DATEDIF(DATE(2025,3,15), DATE(2026,1,5), "D")` | 296 | total days | If the start date is later than the end date you get `#NUM!`. For a plain day count, subtract the dates instead, as Microsoft recommends. ## The classic mistakes: symptom to fix When a formula returns something wrong or odd, check this table before rewriting anything. | Symptom | Likely cause | Fix | |---|---|---| | `#N/A` from a lookup for a key that is visibly there | Trailing or leading space; or number vs text mismatch | `TRIM` the key, check `ISNUMBER`, convert with `VALUE` | | Lookup returns a plausible price for a missing key | VLOOKUP or MATCH using the approximate default | Write `FALSE` or `0`; or use XLOOKUP | | First row correct, later rows `#N/A` after filling down | Lookup table not locked with `$` | `$A$2:$A$7`, press `F4` | | VLOOKUP returns the wrong column after the table changed | Hard-coded column number | Use XLOOKUP or `INDEX`/`MATCH` | | Every value is `0` and no errors are visible | `IFERROR(..., 0)` hiding `#REF!` or a bad column | Remove IFERROR to see it; use `IFNA` or `if_not_found` | | `SUMIFS` total is too low | Text-numbers or stray spaces in the criteria range or sum range | Convert or clean the data | | `SUMIFS` returns `#VALUE!` | Range arguments of different sizes | Make every range the same size | | `SUMIF` / `SUMIFS` returns 0 or an odd number | Mixed up the argument order (sum range third in SUMIF, first in SUMIFS) | Rewrite with `SUMIFS(sum_range, ...)` | | Date arithmetic returns `#VALUE!` | A date stored as text | `DATEVALUE` or `DATE(y, m, d)` | | A date shows as a 5-digit number | Cell formatted as General | Apply a date format | | `AVERAGEIFS` returns `#DIV/0!` | No row matched | Check the criteria, do not mask it blindly | ## Your turn: predict the result ```exercise [ { "type": "predict", "task": "B4 holds 2026-01-30. What date does =EDATE(B4, 1) return? Give it as yyyy-mm-dd.", "accept": ["2026-02-28"], "hint": "February 2026 has 28 days, and there is no 30th, so EDATE returns the last day of that month." }, { "type": "predict", "task": "How many whole working days does =NETWORKDAYS(DATE(2026,1,5), DATE(2026,1,16)) count? Both dates are included, Jan 5 is a Monday and Jan 16 a Friday.", "accept": ["10"], "hint": "Two full Monday-to-Friday weeks." } ] ``` Check yourself before moving on: ```quiz [ {"q": "What is a date in Excel, underneath?", "choices": ["A number counting days, shown through a date format", "A pair of numbers for month and day", "A reference to the system clock", "A text string in a special format"], "answer": 0, "explain": "Dates are serial numbers: 1 is January 1, 1900 by default. Formatting decides whether you see 46027 or 2026-01-05.", "why": [null, "A date is a single number.", "Only TODAY reads the clock; a stored date is a fixed number.", "Text dates are the broken case, not the normal one."]}, {"q": "Which formula gives the last day of the same month as the date in B4?", "choices": ["=B4+30", "=EDATE(B4, 1)", "=EOMONTH(B4, 0)", "=EOMONTH(B4, 1)"], "answer": 2, "explain": "EOMONTH with months set to 0 stays in the same month and returns its last day. EDATE keeps the day-of-month, and adding 30 days ignores month lengths.", "why": ["Month lengths vary, so 30 days is not a month.", "EDATE moves one month ahead and keeps the day number.", null, "That would return the end of the following month."]}, {"q": "What does Microsoft advise about DATEDIF?", "choices": ["It can give incorrect results in some cases, and the MD unit has known limitations", "It only works in Excel 2010", "It always returns days", "Use it for all date math"], "answer": 0, "explain": "DATEDIF is kept for compatibility. Microsoft warns about incorrect results and advises against the MD unit; subtract dates for a plain day count."} ] ``` ## Recap 1. A date is a serial number (1 is January 1, 1900); formatting makes it look like a date, and subtraction gives days. 2. If `ISNUMBER` is false, the date is text and must be converted; build dates with `DATE(y, m, d)`. 3. `EDATE` moves by months and clips to the month's last day; `EOMONTH(date, 0)` is month end, and `EOMONTH(date, -1) + 1` is month start. 4. `NETWORKDAYS(start, end, [holidays])` counts weekdays inclusive; `DATEDIF` works but has caveats, so avoid `"MD"`. 5. Most wrong answers come from the same handful of causes: text-numbers, spaces, unlocked ranges, approximate matches, and hidden errors. Where to go from here: summarize all this with pivot tables in [Excel Pivot Tables and Charts](/guides/excel-pivot-tables-and-charts), or go further with [Advanced Excel](/guides/advanced-excel).