Formulas II: Text, Dates & Aggregation

Excel Track · Branch: Excel Core · Yellow belt · Node EX1.01 · ~3–4 focused hours

You already know the grid from the white belt: the reference (A1, $A$1, mixed), the fill handle, and the arithmetic of SUM/AVERAGE/IF. That was Formulas I, doing arithmetic to clean numbers you already had. Real analyst work almost never starts there. It starts with an export: a GST portal dump, a bank statement, a payroll CSV, an ERP extract. The "numbers" are half text, and the dates are strings in the wrong regional order. The categories are buried inside a code, and nothing adds up yet by anything you care about. Formulas II is the craft of turning that raw export into a clean table you can query, and then asking it questions. You'll pull the signal out of a messy string and do arithmetic on calendars. Then you'll total a column by condition without ever building a pivot table.

Everything that follows rests on three ideas. First, text is data. A cell holding 27AABCU9603R1ZM/INV-00427 contains at least three facts: a state, a taxpayer and an invoice serial. Excel's text functions are the scalpel that separates them.

Second, dates are numbers wearing a costume. Every date in Excel is an integer counting days from a fixed origin. That's why you can subtract two dates to get a duration or add 30 to get a due date. It's also why a "date" that won't do arithmetic is really text in disguise.

Third, the plural conditional-aggregation functions (SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS) are your daily workhorses. 80% of ad-hoc analysis is "total this where that is true", and these five answer it in one line.

After those come SUBTOTAL and AGGREGATE. They're totals that respect filters and step around errors. Last comes a first taste of the array mindset, which the lookups and dynamic arrays ahead will make central. Everything here assumes Microsoft 365. Where a function is younger than Excel 2021 or 2019, the text flags it in one line, because you'll meet analysts still on those builds.

Learning objectives

By the end you can:

1. Extract structured fields from a messy text cell using LEFT, RIGHT, MID, LEN, and the modern TEXTBEFORE / TEXTAFTER / TEXTSPLIT, and choose position-based versus delimiter-based extraction correctly.
2. Clean imported text with TRIM, CLEAN, and SUBSTITUTE (including the non-breaking-space CHAR(160) trap), normalise case with UPPER / LOWER / PROPER, and convert cleaned digits back to real numbers with VALUE.
3. Assemble display strings with TEXT, CONCAT, and TEXTJOIN, and explain why TEXT (formatting to a string) is the inverse skill of VALUE (parsing a string to a number).
4. Explain that Excel dates and times are serial numbers, and use that fact to compute durations, due dates, and ages by direct arithmetic.
5. Do calendar arithmetic with EOMONTH, EDATE, NETWORKDAYS / NETWORKDAYS.INTL, DATEDIF, YEARFRAC, and WEEKDAY, choosing the right function and the right YEARFRAC/weekend basis.
6. Aggregate a column by one or many conditions with SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS, and state precisely why the plural *IFS forms (sum-range first, criteria pairs after) are safer and stronger than the legacy singular SUMIF.
7. Choose SUBTOTAL or AGGREGATE over plain SUM when totals must ignore filtered/hidden rows or step over error cells, and pick the correct function-number and options code.
8. Diagnose the three failures that silently break conditional aggregation (numbers stored as text, dates stored as text, and criteria/argument-order mistakes) and repair each.

Prerequisites & connections

Builds on. EX0.01 (the grid, references, the fill handle, F4 to cycle absolute/relative), EX0.02 (IF, SUM, AVERAGE, ROUND, and the idea of a function's argument list), and the whole habit of naming ranges and reading the formula bar. From the analyst path it leans on M1.06 (receivables and inventory, where invoice dates and ageing buckets live) and anticipates M2.05 (benchmarking, which is conditional aggregation applied to peer sets).

Feeds forward. EX1.02 (Lookups Mastery: XLOOKUP, INDEX/MATCH) is the natural sequel. Pass either form of the mastery check and EX1.02 unlocks. Once you can clean and key a table, you can join tables. SUMIFS and a lookup are two sides of the same coin. One totals by a key and the other fetches by a key, and expert modelling reaches for whichever the shape of the answer wants. EX1.03 and EX2 (Tables, PivotTables, Power Query) take everything here and make it refreshable: structured-reference SUMIFS over an Excel Table, then the same logic pushed into a query pipeline. The array-mindset preview at the end is the on-ramp to EX1.02's lookups and EX2's dynamic arrays, which make that mindset central. EX1.02 and EX2 also bring the functions that make it powerful (FILTER, UNIQUE, SORT, SEQUENCE), and eventually EX5's LAMBDA libraries follow. Every forensic and modelling module downstream assumes you can, without thinking, get a clean number out of a dirty cell and total it by a condition.

Core teaching content

Dates are serial numbers

Start here, because everything else follows from it. Excel doesn't store a date as the text "20-Jul-2026". It stores an integer, the number of days since a fixed origin, and only displays that integer in a date format. In the default 1900 date system, day 1 is 1 January 1900, and the count runs forward from there. So 1 January 2025 is stored as 45658, 1 January 2026 as 46023, and today, 20 July 2026, as 46223. Times are the fractional part. A time of 6:00 AM is 0.25 of a day, so 45658.25 is 1 Jan 2025 at 06:00.

Two consequences make this the most useful fact on the page. First, dates do arithmetic. A date minus a date is a count of days, so =DATE(2025,12,31) - DATE(2025,1,1) returns 364, because 45657 − 45293 = 364. A date plus a number is a later date. An invoice dated in A2 with net-30 terms is due =A2+30.

Second, if a date won't do arithmetic, it's text that looks like a date. You'll meet this import defect more often than any other. A column that reads 31/03/2026 but sits left-aligned and won't subtract is a string. Every date function will ignore it or error on it until you convert it (Common Mistakes below shows how). The DATE(year, month, day) function is the safe way to build a real serial date from parts, and it's immune to regional-format ambiguity.

One short version note. Excel deliberately keeps a historical bug. It treats 1900 as a leap year, which it wasn't, so serials for dates before 1 March 1900 are off by one. It never matters for financial dates. Just know it exists so it never surprises you. There's also the Mac 1904 date system. If serials look ~1,462 days off between a Windows and a Mac workbook, that's why.

The number behind a date is easy to see. Select the cell and press Ctrl+1 ▸ Number ▸ General, or multiply by 1 in a scratch cell with =A2*1. To turn a plain serial into a readable date, apply any date format. Ctrl+Shift+# gives the default dd-mmm-yy.

SUBTOTAL and AGGREGATE

The problem plain SUM cannot solve. SUM(B2:B100) adds every row in the range, always. That includes rows a filter has hidden. It also includes any error cell, and one error makes the whole SUM error out. Two functions fix these two blind spots.

SUBTOTAL: totals that respect the filter. SUBTOTAL(function_num, range) computes an aggregate that ignores rows hidden by a filter. It also ignores any other SUBTOTALs inside its range, so nested subtotals never double-count. The function_num selects the operation: 9 = SUM, 1 = AVERAGE, 2 = COUNT, 3 = COUNTA, 4 = MAX, 5 = MIN. The 1xx variants (109, 101, …) also ignore rows you hid by hand with right-click ▸ Hide, on top of filtered rows. Take a column whose visible rows are 100, 300 and 400, with a 200 filtered out of view. =SUBTOTAL(109, range) returns 800, and it tracks the filter as you change it. That's why the total row of every well-built Excel Table uses SUBTOTAL, and why the status bar's sum at the bottom right reflects only visible rows. SUBTOTAL doesn't ignore errors, though.

AGGREGATE, SUBTOTAL's stronger successor, and the error-skipper. AGGREGATE(function_num, options, range) extends SUBTOTAL with more functions, up to 19, including LARGE, SMALL, PERCENTILE and MEDIAN. More usefully, it adds an options code that can ignore error values. The options: 0 ignore nested SUBTOTAL/AGGREGATE only; 3 ignore hidden rows and errors and nested; 6 ignore error values; 7 ignore hidden rows and errors. Say a column 100, 200, 300, 400 has an error cell lurking that makes SUM fail. =AGGREGATE(9, 6, range) steps over the error and still returns 1000. Reach for AGGREGATE when a range legitimately contains errors you want to survive, such as a division column with the odd #DIV/0!. Reach for SUBTOTAL when you just want filter-aware totals. Both beat plain SUM the moment a range is filtered or dirty. AGGREGATE needs Excel 2010+.

The array mindset (a preview)

You've already met arrays twice without naming them. TEXTSPLIT spilled three cells from one formula, and the {"/","-"} and {"2025-11-27"} constants handed a set of values to a function. That's the array mindset. You think in whole ranges and let one formula produce many results, where before you'd write a formula per cell and drag it down.

In Microsoft 365 this is the dynamic array engine. A formula that returns multiple values spills into the neighbouring cells on its own, marked by a blue border. You reference the whole spilled block with the spill operator #, so =SUM(D2#) totals whatever D2's formula spilled. The functions that make this powerful arrive later in the track. FILTER returns only rows meeting a condition and UNIQUE returns distinct values. SORT and SEQUENCE round out the set.

You can already see where it's heading, though. Spill UNIQUE(States) down the side of a summary, put a SUMIFS beside it that references each spilled cell, and you get a self-sizing report that grows as new states appear in the data. The *IFS functions you've just learned are the aggregation half of that pattern, and dynamic arrays are the structure half.

For now the habit to build is small. Whenever you catch yourself about to copy a formula down a thousand rows, pause and ask whether one array formula could do it once. Half the time, in modern Excel, it can. That instinct separates a fluent analyst from a fast typist.

Common mistakes & how experts think differently

1. Numbers stored as text break SUMIFS without warning (and SUM, AVERAGE and every other range total). An ERP or CSV export often delivers digits as text: a store ID "1001", an amount "1,25,000" with grouping, a value with a trailing space. SUMIFS adds only real numbers in its sum range, so every amount that arrived as text contributes nothing. The total comes back 0, or short by those rows, with no error at all. The criterion side is more forgiving than people expect. An equals criterion reads text that looks like a number as that number, so SUMIFS(Value, StoreID, 1001) still finds a StoreID held as "1001". Exact-match lookups are stricter. XLOOKUP(1001, StoreID, Value) returns #N/A against the same column. Watch for left-aligned "numbers", a green triangle in the corner, =ISNUMBER(A2) returning FALSE, and a SUM that reads 0. To fix it, convert with VALUE, multiply by 1 (=A2*1), run Data ▸ Text to Columns ▸ Finish, or select the range and use the green-triangle warning's "Convert to Number". For grouped currency, strip symbols and separators first. =VALUE(SUBSTITUTE(SUBSTITUTE(A2, "₹", ""), ",", "")) turns "₹1,25,000" into the number 125000. Experts sanity-check every imported numeric column with ISNUMBER before they trust a single aggregate.
2. Dates stored as text, and the dd/mm vs mm/dd regional trap. A "date" column that won't subtract is text. Worse, the same string 03/04/2026 is 4 March in a US (mm/dd) locale and 3 April in an Indian or UK (dd/mm) locale. A mis-parsed import can shift every date by weeks without raising a single error, and wreck an ageing report. Experts build dates from unambiguous parts with DATE(year, month, day), or use DATEVALUE with a known locale. They verify with =ISNUMBER(A2), and they don't trust a date column until it does arithmetic.
3. SUMIF (singular) versus SUMIFS (plural) argument order. SUMIF(range, criteria, sum_range) puts the sum range last. SUMIFS(sum_range, criteria_range, criteria) puts it first. Mixing them up is the most common formula error in the language. Experts drop the singular forms entirely and always use SUMIFS/COUNTIFS/AVERAGEIFS. The order stays the same, you get more conditions, and there's nothing to remember.
4. Using SUM over filtered data. Filter a table, SUM the visible column, and you get the total of all rows, hidden ones included. It's a wrong number that looks right. Totals over any data that will be filtered must use SUBTOTAL(9, …) or 109. Totals over ranges that may hold errors use AGGREGATE(9, 6, …). Experts make the total row of every working table a SUBTOTAL by reflex.
5. TRIM won't remove non-breaking spaces. Web and PDF text carries CHAR(160). You can't see it and TRIM can't touch it, so two "identical" names refuse to match. =TRIM(SUBSTITUTE(A2, CHAR(160), " ")) is the repair. CLEAN handles the other non-printing characters.
6. Forgetting that LEFT/MID/TEXT return text. A field pulled out of a code with LEFT or MID is a string, even if it looks numeric, and it won't sum until you wrap it in VALUE. The same goes the other way. TEXT(B2,"#,##0") is a label, so don't feed it back into arithmetic. Experts track the type of every intermediate cell as well as how it looks.
7. Hard-coding criteria instead of referencing a cell. =SUMIFS(Sales, Region, "West") works once. =SUMIFS(Sales, Region, G2) builds a summary block that recomputes for every label you list down column G. Keep criteria in cells and point formulas at them. That reusable-report instinct turns a one-off answer into a template.
8. Assuming the modern text functions exist everywhere. TEXTBEFORE, TEXTAFTER and TEXTSPLIT are Microsoft 365/2024 only, and TEXTJOIN/MAXIFS/MINIFS need 2019+. On a 2016/2019 machine you fall back to FIND/MID/LEFT nesting. Experts check their audience's Excel version before they ship a workbook. A #NAME? error on the recipient's screen is an embarrassment you can avoid.
9. Wrong YEARFRAC basis (or ignoring it). 30/360, actual/365 and actual/actual give different accruals over the same dates. Quoting an interest number without its day-count basis is like quoting EBITDA without saying whether other income is in it. State the basis every time.
10. Stopping at the parse instead of validating it. A beginner extracts a state code with LEFT(A2,2) and moves on. The expert adds =IF(LEN(A2)<>15, "BAD GSTIN", "ok") and a COUNTIFS reconciliation, so a malformed row announces itself before it can poison a total three steps later. A parse you haven't validated is a bug you haven't found yet.

Worked examples

Worked example 1: Parsing messy GST invoice strings and totalling by state (India)

The situation. A finance associate exports the month's sales register from the GST portal. It arrives as a single column where each cell jams the customer's GSTIN and the invoice number together, like 27AABCU9603R1ZM/INV-00427. The value of each invoice sits in the next column, in ₹. The controller wants the total invoice value by state. The state, though, is buried in the first two digits of the 15-character GSTIN. That's the GST state code: 27 = Maharashtra, 29 = Karnataka, 07 = Delhi, 24 = Gujarat. The answer should be no pivot table, just a live formula block that updates when new rows arrive.

The data (A2:B11).

Step 1: split the compound string. The delimiter between GSTIN and invoice is /. In C2, pull the GSTIN and in D2 the invoice part:

C2:  =TEXTBEFORE(A2, "/")     →  27AABCU9603R1ZM
D2:  =TEXTAFTER(A2, "/")      →  INV-00427

Validate the GSTIN before you trust it. A valid one is 15 characters long, so the check is =IF(LEN(C2)<>15, "CHECK", "ok"). =LEN("27AABCU9603R1ZM") returns 15, so row 2 is clean.

Step 2, extract the two fields you actually want. The state code is the first two characters of the GSTIN. The invoice serial is whatever follows the last dash:

E2:  =LEFT(C2, 2)            →  27          (state code)
F2:  =TEXTAFTER(D2, "-")     →  00427       (invoice serial)

You can also do the whole parse in one dynamic-array formula by splitting on both delimiters at once. =TEXTSPLIT(A2, {"/","-"}) spills three cells across: 27AABCU9603R1ZM, INV, 00427. Fill C2:F2 down through row 11.

Step 3: total by state with SUMIFS. Lay the four state codes in H2:H5 and total each. The sum range comes first:

I2:  =SUMIFS($B$2:$B$11, $E$2:$E$11, H2)      ' total ₹ for the state in H2
J2:  =COUNTIFS($E$2:$E$11, H2)                ' number of invoices
K2:  =AVERAGEIFS($B$2:$B$11, $E$2:$E$11, H2)  ' average invoice value
L2:  =MAXIFS($B$2:$B$11, $E$2:$E$11, H2)      ' largest single invoice

Step 4: the results (Python-verified).

The grand total across all four states is ₹8,20,500. Confirm it with =SUM(I2:I5) and cross-check against =SUM(B2:B11). They must agree, and they do. The largest single invoice in Maharashtra (MAXIFS) is ₹1,25,000.

The validation from Step 1 pays off now. Suppose a GSTIN were malformed. Its state code would fall outside 27/29/07/24, its value would be missing from the four-state total, and SUM(I2:I5) wouldn't equal SUM(B2:B11). That mismatch is your tripwire. A clean split, a validation and four SUMIFS answer the controller's question, with a block that recomputes the moment next month's rows are pasted in.

Worked example 2: Pay-period working days and wages by department (US)

The situation. A US operations analyst runs a bi-weekly payroll for the period Monday 17 Nov 2025 through Friday 28 Nov 2025. Thanksgiving (Thursday 27 Nov 2025) is a paid company holiday and a non-working day. She needs two numbers. The first is the count of working days in the period. She'll use it to check expected hours and to prorate a daily-rate contractor. The second is total wages by department across the ten employees on the run.

Step 1, count working days with NETWORKDAYS.INTL. Put the period start in F1 (2025-11-17), the end in F2 (2025-11-28), and the holiday date in H2 (2025-11-27). The default weekend (Saturday/Sunday) is weekend code 1:

F4:  =NETWORKDAYS.INTL(F1, F2, 1, H2:H2)   →  9    ' Mon–Fri both weeks, minus Thanksgiving

There are ten weekdays in the span. Thanksgiving removes one, leaving 9. As a sanity check, =NETWORKDAYS.INTL(F1, F2, 1) without the holiday returns 10. Now suppose this were a six-day-week operation with only Sundays off. The weekend string "0000001" runs Mon to Sun with 1 = off, and =NETWORKDAYS.INTL(F1, F2, "0000001", H2:H2) gives 10. Only Thanksgiving and the single Sunday, 23 Nov, are excluded. So expected full-time hours for the period are =F4 * 8 = 72 hours, and a contractor billing $600/day earns =F4 * 600 = $5,400.

Step 2, the wage table (A2:C11).

Step 3, total wages by department with SUMIFS. List the three departments in E2:E4 and total each. As before, the sum range goes first, then the (criteria_range, criteria) pair:

F2:  =SUMIFS($C$2:$C$11, $B$2:$B$11, E2)      ' total wages for the dept in E2
G2:  =COUNTIFS($B$2:$B$11, E2)                ' headcount
H2:  =AVERAGEIFS($C$2:$C$11, $B$2:$B$11, E2)  ' average wage
I2:  =MINIFS($C$2:$C$11, $B$2:$B$11, E2)      ' lowest wage in the dept

Step 4: results (Python-verified).

The company-wide wage bill is =SUM(F2:F4) = $35,400. That must equal =SUM(C2:C11), and it does, the same reconciliation habit as in Worked Example 1.

Look at how the two halves of what you've learned combine in one small report. A date function sized the period and set expected hours. A conditional-aggregation function rolled the wages up by a text key. First you parse and clean. Then you count and total. That's the shape of nearly every operational analysis you'll ever run.

Practice set

Work each problem before you read its solution. Figures are synthetic unless named. Assume Microsoft 365 unless a version note appears. P1 to P3 are guided and P4 to P9 independent. P10 to P12 are timed. Every solution begins Solution.

P1 (guided). Clean and re-key a customer name. Cell A2 holds " reliance industries ", two leading spaces, three between the words, two trailing. Write one formula that returns a clean, title-cased name, and state its final character count.

Solution. =PROPER(TRIM(A2)). TRIM strips the leading and trailing spaces and collapses the internal run to a single space. That turns the 25-character string into "reliance industries", which is 19 characters. PROPER then title-cases it to "Reliance Industries". The final length is 19. If the source came from a web export, guard against non-breaking spaces first: =PROPER(TRIM(SUBSTITUTE(A2, CHAR(160), " "))).

P2 (guided). Extract fields from a product code. Cell A2 holds "IN-MUM-4472-A". Return (a) its length, (b) the country prefix, (c) the city, and (d) the numeric part as a real number.

Solution. (a) =LEN(A2) = 13. (b) =TEXTBEFORE(A2, "-") = "IN", or =LEFT(A2, 2). (c) =TEXTBEFORE(TEXTAFTER(A2, "-"), "-") = "MUM", or =MID(A2, 4, 3). (d) The 4472 sits between the second and third dash. =TEXTAFTER(A2, "-", 2) gives "4472-A", so =TEXTBEFORE(TEXTAFTER(A2, "-", 2), "-") isolates "4472". Wrap that in VALUE and you get the number 4472. The tidiest one-shot is =TEXTSPLIT(A2, "-"). It spills {"IN","MUM","4472","A"} across four cells, and then you VALUE the third one for maths.

P3 (guided). Strip currency formatting to a number. Cell A2 holds the text "₹1,25,000". Convert it to a number SUMIFS can use.

Solution. Remove the symbol and the separators, then coerce: =VALUE(SUBSTITUTE(SUBSTITUTE(A2, "₹", ""), ",", "")). The inner SUBSTITUTE drops the ₹ and the outer one drops the commas, which leaves the string "125000". VALUE then returns the number 125000. Check that =ISNUMBER(...) returns TRUE. Only then will it aggregate.

P4. TEXTJOIN a clean list. Cells A1:A4 hold Mumbai, (blank), Pune, Nagpur. Produce a single comma-separated string with no doubled commas.

Solution. =TEXTJOIN(", ", TRUE, A1:A4) = "Mumbai, Pune, Nagpur". The TRUE second argument tells TEXTJOIN to skip the empty cell, so the delimiter appears only between real values. With =A1&", "&A2&", "&A3&", "&A4 instead, the blank would produce "Mumbai, , Pune, Nagpur". That's the defect TEXTJOIN exists to prevent.

P5. Serial-number arithmetic. Without formatting anything as a date, how many days are there from DATE(2025,1,1) to DATE(2025,12,31), and what is the Excel serial number of 2025-01-01?

Solution. A date minus a date is a day count, so =DATE(2025,12,31) - DATE(2025,1,1) = 364. A non-leap year has 365 days, and the gap between its first and last day is one less than that. The serial of 2025-01-01 is 45658. Verify it with =DATE(2025,1,1)*1, which forces the underlying integer to show. It's also why =A2+30 gives a net-30 due date. You're adding 30 to an integer.

P6. Month-end and monthly cadence. An invoice is dated 2026-03-18. Give (a) its net-30 due date, (b) its "end of month + 15 days" due date, and (c) the same-day date three months later.

Solution. (a) =DATE(2026,3,18) + 30 = 2026-04-17. (b) =EOMONTH("2026-03-18", 0) + 15. EOMONTH(...,0) returns 2026-03-31, and 15 days on from that is 2026-04-15. (c) =EDATE("2026-03-18", 3) = 2026-06-18. The term structures in (a) and (b) differ by two days. That's trivial on one invoice, and decisive when it decides which 30-day ageing bucket a receivable falls into.

P7. Working days with a holiday. Count the working days in January 2026 given two public holidays: New Year's Day (2026-01-01) and Republic Day (2026-01-26). What day of the week is 1 Jan 2026?

Solution. =NETWORKDAYS("2026-01-01", "2026-01-31", {"2026-01-01";"2026-01-26"}) = 20. January 2026 has 22 weekdays. Both holidays fall on weekdays, so they remove two, leaving 20. =WEEKDAY("2026-01-01", 2) = 4, so New Year's Day 2026 is a Thursday, since return-type 2 makes Monday = 1. Working-day counts like this drive monthly capacity, accrual and SLA maths.

P8. Tenure with DATEDIF. An employee was hired 2019-03-15. As of today, 2026-07-20, express their tenure as complete years, then the leftover months and days, then as total whole months.

Solution. =DATEDIF("2019-03-15", "2026-07-20", "Y") = 7 complete years. "YM" = 4, the months beyond the whole years, and "MD" = 5, the days beyond the whole months. So the answer reads "7 years, 4 months, 5 days." For total whole months, =DATEDIF("2019-03-15", "2026-07-20", "M") = 88. The argument order is start, end, unit, and reversing the dates gives an error.

P9. Interest by day-count basis. Compute simple interest on ₹10,00,000 at 8% per annum from 2025-01-15 to 2025-07-15 under the 30/360 (US) basis.

Solution. =1000000 * 0.08 * YEARFRAC("2025-01-15", "2025-07-15", 0). Under 30/360 (basis 0) the period is half a year, so YEARFRAC = 0.5 and interest = 1000000 × 0.08 × 0.5 = ₹40,000. State the basis whenever you quote an accrual. On actual/365 (basis 3), the same span of 181 days gives a slightly different fraction, ≈0.4959, and so ≈₹39,671. The dates haven't moved. Only the convention changed, and the number changed with it.

P10 (timed: 5 min). Two-condition SUMIFS and its family. A sales table has Region in A2:A7 = {West, West, East, West, West, North}, Quarter in B2:B7 = {Q3, Q3, Q3, Q2, Q3, Q3}, and Amount in C2:C7 = {120, 80, 200, 150, 95, 60}. Compute the total, count, average, and maximum Amount for West and Q3.

Solution. =SUMIFS(C2:C7, A2:A7, "West", B2:B7, "Q3") totals the three qualifying rows (120, 80, 95) to 295. =COUNTIFS(A2:A7, "West", B2:B7, "Q3") = 3. =AVERAGEIFS(C2:C7, A2:A7, "West", B2:B7, "Q3") = 295 ÷ 3 = 98.33. =MAXIFS(C2:C7, A2:A7, "West", B2:B7, "Q3") = 120. The East row, which is Q3 but not West, and the West/Q2 row are both correctly left out, because multiple *IFS conditions are ANDed.

P11 (timed: 5 min). Filter-aware and error-proof totals. A column of daily figures reads 100, 200, 300, 400. (a) After a filter hides the 200 row, what does a visible-only SUM return? (b) If instead one cell is a #DIV/0! error, write a total that ignores it.

Solution. (a) =SUBTOTAL(109, range) returns the total of the visible rows only. With 200 filtered out, that's 100 + 300 + 400 = 800. Plain =SUM(range) would still return 1000, which is the classic filtered-total error. The 109 code sums while ignoring both filtered and manually hidden rows. (b) =AGGREGATE(9, 6, range) sums with options code 6 = "ignore error values". It steps over the #DIV/0! and returns 100 + 200 + 300 + 400 = 1000, where plain SUM would itself error out. SUBTOTAL tracks filters, and AGGREGATE survives errors as well.

P12 (timed: 6 min). Diagnose a broken SUMIFS. An analyst writes =SUMIFS(Value, StoreID, 1001) to total sales for store 1001 and gets 0, even though rows for store 1001 clearly exist. The table was imported from a CSV. State the likely cause, the one-cell test that confirms it, and two fixes.

Solution. The Value column holds numbers stored as text ("48500", left-aligned, green triangle). SUMIFS adds only real numbers in its sum range, so every matching row contributes nothing and the total reads 0. Confirm it with =ISNUMBER(Value_cell), which returns FALSE. There are two fixes. (1) Convert the column to real numbers: select it, then Data ▸ Text to Columns ▸ Finish, or use the green-triangle warning's "Convert to Number". (2) Add a helper column of =VALUE(amount_cell), or the amount times 1, and point SUMIFS at it. A StoreID held as text isn't the culprit, because an equals criterion matches the number 1001 against the text "1001". It would break an XLOOKUP on store IDs, though, so test that column with ISNUMBER as well. Make one habit durable. ISNUMBER-check every imported numeric column before you aggregate, because a text-number SUMIFS fails without any warning, and that's far more dangerous than an error.

Applied mini-project

Build a monthly ledger summariser.

You're handed a raw monthly transaction export, the kind every ERP, bank and payment gateway produces. Your job is to turn it into a one-screen management summary that recomputes when next month's data is pasted in. Everything you've practised goes into this one deliverable: clean the text, parse the dates, aggregate by condition.

The raw data (one sheet, Ledger). Assume ~40 to 60 rows, deliberately dirty the way real exports are. TxnCode is a compound string like "MH/SALE/2026-04-03/INV-1043" that mixes state, type, date and reference. Party holds customer and vendor names with inconsistent spacing and case. In Amount, some cells are text with ₹ and thousands separators. Notes is free text that occasionally holds a non-breaking space. If you don't have a file, fabricate 40 rows across four states (MH, KA, DL, GJ), two transaction types (SALE, REFUND) and dates spanning April 2026.

Build (helper columns first, summary block second):

1. Parse TxnCode into four clean columns with TEXTSPLIT(TxnCode, "/"), which spills four columns (State, Type, DateText, Ref). Use TEXTBEFORE/TEXTAFTER instead if you prefer explicit control. Convert DateText to a real date with DATEVALUE, or DATE on split parts, and confirm every one is a number with ISNUMBER.
2. Clean Party to a canonical key: =PROPER(TRIM(SUBSTITUTE(Party, CHAR(160), " "))). Verify with a COUNTIF that near-duplicates have collapsed. The same customer should now show one spelling where it used to show three.
3. Coerce Amount to real numbers with =VALUE(SUBSTITUTE(SUBSTITUTE(Amount, "₹", ""), ",", "")), and ISNUMBER-check the whole column. Nothing downstream works until this is clean.
4. Add date fields: month-end with =EOMONTH(Date, 0), and a weekday flag =WEEKDAY(Date, 2) to spot weekend-dated entries worth a second look.
5. Build the summary block, which is the deliverable:

• Net sales by state: =SUMIFS(Amount, State, H2, Type, "SALE") down a list of the four state codes.
• Refund count and value by state with COUNTIFS/SUMIFS, and the largest single sale per state with MAXIFS.
• A grand-total row that must reconcile. The SUM of your by-state sales plus refunds equals the SUM of the cleaned Amount column. If they disagree, a parse failed, so hunt it down.
• A filter-aware total using SUBTOTAL(9, Amount), so that when you filter the ledger to one party or date range, the headline total tracks the filter.

1. Write a one-paragraph memo. Say which state drove the month and give the refund rate (refund value ÷ sales value) by state. Name one data-quality problem you found and fixed during cleaning, and the single formula you'd reuse next month unchanged.

Rubric (self-score; 0 = missing/wrong, 1 = partial, 2 = correct and defended): clean parse of TxnCode with ISNUMBER-verified dates; Party canonicalised (near-duplicates collapsed); Amount coerced and reconciled; summary block driven by cell-referenced criteria (not hard-coded); grand-total reconciliation ties out; memo carries numbers, not adjectives. Pass: ≥ 9/12, no zero on the reconciliation criterion. A summary that doesn't tie to the source is only a guess.

Lab: open the Lookups & aggregation database workbook lab in the Practice ▸ Excel tab and pass its checks.

Reading & resources

Core references (reach for them in this order):

• Microsoft Support, the official function pages for TEXTSPLIT, TEXTBEFORE/TEXTAFTER, SUMIFS, NETWORKDAYS.INTL, AGGREGATE, and YEARFRAC. Each page has a copyable example table. When a function's argument order slips your mind, it's the fastest ground truth. [Free] [Beginner]
• ExcelJet (exceljet.net), with concise, example-first entries for every function here, plus its "Excel functions for text" and "date and time" roundups. It's the best free reference for "what does this return on this input." [Free] [Beginner]
• Chandoo.org, practical SUMIFS/COUNTIFS and data-cleaning walkthroughs written for analysts, full of the same messy real data you've been cleaning here. [Free] [Beginner]

Going deeper:

• Microsoft, "Dynamic array formulas and spilled array behavior." Read this now as the bridge from the array-mindset preview to EX1.02/EX2. It explains the # spill operator and how UNIQUE+SUMIFS build self-sizing reports. [Free] [Intermediate]
• Mike Girvin, Ctrl+Shift+Enter: Mastering Excel Array Formulas, the canonical treatment of the array thinking previewed here. It's heavier than you need today, and invaluable once you reach dynamic arrays. [Paid] [Advanced]
• ISDA/loan-market day-count primers (any bank's "30/360 vs actual/actual" one-pager), the finance context behind YEARFRAC's basis argument, so your accruals match the market's convention. [Free] [Intermediate]
• Purna Duggirala / John Walkenbach, Excel Formulas (the "Bible" formulas volume), the TEXT format-code catalogue and the full SUBTOTAL/AGGREGATE function/option tables, worth bookmarking. [Paid] [Intermediate]

The Modern Analyst's Addendum

Everything above teaches this skill from first principles, by hand, that is how you learn it, and the mastery check still tests it that way. This addendum shows how a working analyst amplifies the same skill today. It adds; it never replaces. (R1/R10)

AI-Augment this skill

{
  "skill": "Extracting and aggregating text/date fields reliably -- TEXTBEFORE/TEXTAFTER/TEXTSPLIT, date serials, EOMONTH/YEARFRAC, and the SUMIFS/COUNTIFS/AVERAGEIFS/MAXIFS/MINIFS/SUBTOTAL/AGGREGATE family",
  "use": "Ask an assistant to draft the Power Query (M) or pandas translation of a text/date transformation you already wrote as an Excel formula, then run all versions on the SAME raw data and diff the totals row for row instead of trusting any one of them alone.",
  "tools": ["Power Query (Get & Transform Data) inside Excel", "pandas in a notebook (Jupyter or Google Colab)", "a general-purpose LLM chat (Claude or ChatGPT) for a first-draft M or pandas translation of a working Excel formula"],
  "prompt": "Here is an Excel formula that splits a GSTIN-plus-invoice string and totals the value by state: <PASTE>. Write the equivalent as (a) a Power Query M transformation and (b) a pandas snippet, using the same field boundaries. Do not change the business logic -- I will compare all three outputs myself.",
  "verify": "Run the same transformation three ways -- the Excel formula, Power Query, and pandas -- on the identical raw rows, and confirm all three grand totals match to the rupee or cent before trusting any one of them. A translation that 'looks right' and a translation that produces the same number on the same data are different claims.",
  "diy": "You must be able to write the TEXTBEFORE/TEXTAFTER-plus-SUMIFS combination by hand and reason correctly about DD-MM vs MM-DD date ambiguity with no tool in the room -- the mastery gate is unaided."
}

Modern Data Analysis

By hand first. The first worked example split a GSTIN-plus-invoice string and rolled the value up by state with SUMIFS to ₹8,20,500. The second parsed a pay period and rolled wages up by department to $35,400. Both were cross-checked against a plain SUM over the raw rows, the reconciliation habit you've been building all along.

Today's workflow. Run the same transformation three ways, as the Excel formula, in Power Query and in pandas, on the identical raw data. A translation error then shows up as a grand total that doesn't match. Without the comparison, you'd just be trusting a formula. This matters most for text parsing and date handling, the riskiest ground you've covered, because on a bad input they produce a plausible-looking wrong number and never an error.

Tools & sources (IN + US). Use pandas' .str accessor and .groupby() for the recomputation below, and Power Query for the identical job inside Excel without leaving the workbook. India. GSTN's own GSTR-1/GSTR-9C return-filing utilities and the e-invoice IRN/QR-code JSON schema (CBIC's e-invoice portal documentation) have the same "structured string glued together with delimiters" shape you've been parsing, at production scale. US. SEC EDGAR's companyfacts API makes a useful contrast case, because its XBRL date tags are consistently ISO-8601 and therefore unambiguous. FRED's date-indexed CSV exports let you practise the same parsing habits on a macro time series.

# recomputed in-session — R3
import pandas as pd

# WE1: GST invoice strings -> state code -> the pandas equivalent of the module's own SUMIFS
raw = [("27AABCU9603R1ZM/INV-00427", 125000), ("29AAACI1234F1Z5/INV-00428", 84000),
       ("07AAGCS5678K1Z2/INV-00429", 56000), ("27AABCU9603R1ZM/INV-00430", 92000),
       ("24AAKCP2345L1Z9/INV-00431", 47500), ("29AAACI1234F1Z5/INV-00432", 133000),
       ("27AABCU9603R1ZM/INV-00433", 61000), ("07AAGCS5678K1Z2/INV-00434", 78000),
       ("24AAKCP2345L1Z9/INV-00435", 39000), ("29AAACI1234F1Z5/INV-00436", 105000)]
df = pd.DataFrame(raw, columns=["invoice_string", "value"])
df["state_code"] = df["invoice_string"].str.split("/").str[0].str[:2]
print("pandas grand total:", df["value"].sum())          # module's own WE1 total, recomputed
assert df["value"].sum() == 820500

# The same six characters, parsed two ways -- month-first (US/pandas default) vs day-first (India)
d_us = pd.to_datetime("03/04/2026")
d_in = pd.to_datetime("03/04/2026", dayfirst=True)
print("month-first:", d_us.date(), " day-first:", d_in.date(), " gap (days):", abs((d_us - d_in).days))

Verify. The pandas grand total reproduces the first worked example's ₹8,20,500 to the rupee. The same .groupby() pattern on the second example's payroll data reproduces the $35,400 company total to the dollar. Both figures are confirmed independently, by recomputing them. "03/04/2026" parsed month-first and day-first lands 30 days apart, on real dates that a NETWORKDAYS.INTL deadline or an ageing-report cutoff would use.

Quantitative lens

A misread date can move a due date or a filter window by weeks without any warning, and you can check the size of the error to the day. Parsed month-first (the pandas and US default), "03/04/2026" reads as 4 March 2026. Parsed day-first (the Indian and UK convention), it reads as 3 April 2026. That's the same six characters and two real dates 30 days apart. Say an imported file assumed the wrong convention without telling anyone. Any ageing bucket, NETWORKDAYS.INTL deadline or net-30 due date built on it is wrong by that full gap, a whole month here. That's a lot more than a rounding error.

The 1900 leap-year bug is real and checkable, and it doesn't do what the folklore around it claims. The version note earlier says, in one line, that it "never matters for financial dates." We verified that directly. Differences computed entirely inside Excel's own serial system, end minus start for dates from 1 March 1900 onward, match the true calendar day count for every pair tested. That's four pairs spanning 2 to over 200 years, and every one is exact. The constant +1-day offset from the phantom 29 February 1900 is added to every modern serial alike, so it cancels in any subtraction done inside Excel. It's worth spelling out, because the folklore overstates the bug. It puts no hidden drift into a tenure, accrual or ageing calculation computed entirely in Excel.

Where that one-line caveat does bite: a date rebuilt from a bare serial number outside Excel, using the "obvious" reading of Excel's own claim. Excel's serial 45658 is 1 January 2025, the figure from earlier. Rebuild it in Python with origin 1899-12-30, the convention that accounts for the phantom day, and you get 1 January 2025. That's correct. Now rebuild it from the origin implied literally by "serial 1 = 1 January 1900", that is 1900-01-01 + (serial − 1) days. You get 2 January 2025, one day ahead, with no error on either side. That's the risk once a .csv export carries bare date serials into a reconciliation script in place of formatted dates.

Limits. The day-count check above covers four illustrative date pairs and isn't an exhaustive proof. The cancellation is a mathematical identity for any two dates on the same side of 1 March 1900, which covers effectively all real analyst work. So it says nothing new about the vanishingly rare calculation that reaches back before 1900. The 30-day DD/MM-vs-MM/DD gap is specific to "03/04/2026". A different pair of digits gives a different gap, sometimes zero. Any date past the 12th of its month can't be misread the other way at all, because there's no 13th month.

Where this goes next: galaxy cross-links

• G2 · DA1.02 pandas I: Data Wrangling. The .str accessor and .groupby() used above are pandas' native versions of the TEXTBEFORE/TEXTAFTER and SUMIFS you've learned here, and they work at scales a spreadsheet can't hold.
• G2 · DA1.03 pandas II: Cleaning, Time Series & the Excel↔Python Bridge. The Quantitative lens above asks one question about one bug: does a date survive the trip between Excel and Python? There, that question becomes the whole subject, taken past this one bug to the full bridge.

Teach it back & journal

Feynman prompt. Write a one-page explainer for a smart 15-year-old titled "The spreadsheet doesn't see what you see." Explain three illusions. The first is that a cell showing 1001 is a number. It might be text, and the computer treats 1001 and "1001" as strangers. The second is that a cell showing 31/03/2026 is a date. It might be a string that refuses to do date arithmetic, and it might even mean a different day in a different country. The third is that a SUM under a filter shows the visible total. It doesn't, because it adds the hidden rows too. For each illusion, give the one-line test that reveals the truth (ISNUMBER, "does it subtract?", SUBTOTAL) and the one-line fix. End with the sentence: "A number you can't add and a date you can't subtract are both just text wearing a costume."

Journal prompt. Pick a real messy export you have lying around (a bank statement, a UPI/credit-card history, a downloaded transactions CSV). Spend twenty minutes turning it into a clean table you can aggregate. Which column fought you hardest? It might be the amounts (text with symbols), the dates (wrong regional order), or a compound code you had to split. Write down the cleaning formula that finally worked. Then answer the question the data can now answer that it couldn't before you cleaned it. "How much did I spend on food this quarter?" is a SUMIFS. Finally, pick one habit you want to make automatic: ISNUMBER-checking imports, reconciling a by-condition total back to the grand SUM, or reaching for SUMIFS over a pivot for a quick answer. Note why a five-second reconciliation is cheaper than a wrong number in a report.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.