Chapter 12: Advanced Functions & Array Formulas

Advanced Functions & Array Formulas — this is one of the most exciting and modern parts of Excel, especially since dynamic arrays arrived in Excel 365 / 2021+ and completely changed how we write formulas.

Today we’ll cover:

• Array formulas (old Ctrl+Shift+Enter style + the new dynamic arrays: FILTER, SORT, UNIQUE, SEQUENCE)
• Multi-condition summing/counting/averaging: SUMIFS, COUNTIFS, AVERAGEIFS
• Flexible & indirect references: INDIRECT, OFFSET, AGGREGATE
• Newer text functions: TEXTBEFORE, TEXTAFTER (and friends)

I’ll explain everything very slowly, step by step, like a patient teacher sitting next to you in Airoli (hope the January weather is treating you well today!). We’ll continue using our tea shop sales data — assume your TeaSales table now has many columns and hundreds of rows (Date, Item, Qty, Price, Total, City, CustomerID, PaymentMode, etc.).

Let’s go!

1. Array Formulas – Legacy vs Dynamic Arrays

Legacy array formulas (pre-2018/2020 Excel)

• You had to press Ctrl + Shift + Enter (CSE) to “activate” them.
• Excel put curly braces {} around the formula automatically.
• Very common before dynamic arrays: {=SUM(IF(…))}

Dynamic arrays (Excel 365 / 2021+ / current 2026 versions)

• No Ctrl+Shift+Enter needed — just press Enter.
• Results spill automatically into neighboring cells (down/right).
• If spill area is blocked → #SPILL! error (clear cells below/right).
• Huge time-saver — no more dragging formulas!

Key modern dynamic array functions

A. UNIQUE – Returns unique values from a list/range

Example – List of unique cities & items In a blank area (say cell H2): =UNIQUE(TeaSales[City]) → spills downward: Mumbai, Navi Mumbai, Thane, Pune, Kalyan (only once each)

In I2: =UNIQUE(TeaSales[Item]) → Chai, Coffee, Biscuit, Pencil, Eraser, etc.

Combine: =UNIQUE(FILTER(TeaSales[Item], TeaSales[City]=”Mumbai”)) → only items sold in Mumbai (unique list)

B. SORT – Sorts a range/array

=SORT(UNIQUE(TeaSales[Item])) → alphabetized unique items

=SORT(TeaSales[Total], , -1) → sorts entire Total column descending (highest sales first)

C. FILTER – The most powerful new function – like Excel’s built-in filter but in formula

Example 1 – All sales in Mumbai =FILTER(TeaSales, TeaSales[City]=”Mumbai”) → spills the entire table but only Mumbai rows!

Example 2 – Items with Qty > 50 in last 30 days =FILTER(TeaSales[[Item]:[Total]], (TeaSales[Qty]>50)*(TeaSales[Date]>=TODAY()-30))

Example 3 – Top 5 total sales rows =SORT(FILTER(TeaSales, TeaSales[Total]>0), 5, -1) (5 = column index of Total)

D. SEQUENCE – Generates number/date sequences

=SEQUENCE(10) → 1 to 10 downward =SEQUENCE(7,1,TODAY()-6,1) → last 7 days dates =SEQUENCE(12) → 1 to 12 (for months)

Combine: =TEXT(SEQUENCE(12,1,DATE(2026,1,1),1),”mmm”) → Jan to Dec

2. SUMIFS, COUNTIFS, AVERAGEIFS (Multi-condition Aggregates)

These are still essential even with dynamic arrays — very fast and readable.

Syntax (all same pattern): =FUNCTION(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Real examples – Tea shop analysis

SUMIFS – Total sales in Mumbai for Chai =SUMIFS(TeaSales[Total], TeaSales[City],”Mumbai”, TeaSales[Item],”Chai”)

COUNTIFS – Number of transactions > ₹1000 in last 7 days =COUNTIFS(TeaSales[Total], “>1000”, TeaSales[Date], “>=”&TODAY()-7)

AVERAGEIFS – Average Qty of Coffee sold in Thane =AVERAGEIFS(TeaSales[Qty], TeaSales[Item],”Coffee”, TeaSales[City],”Thane”)

Wildcards (very useful):

• * = any characters
• ? = single character =SUMIFS(TeaSales[Total], TeaSales[Item],”*Chai*”) → anything containing “Chai”

3. INDIRECT, OFFSET, AGGREGATE

A. INDIRECT – Turns text into real cell reference

Example – Dynamic sheet reference Cell A1 = “Jan-2026” =SUM(INDIRECT(“‘”&A1&”‘!E:E”)) → sums Total column from sheet named in A1

B. OFFSET – Returns a range offset from a starting cell Very flexible but volatile (recalculates often → slow on large files)

Example – Last 7 days sales total (assuming dates sorted) =SUM(OFFSET(TeaSales[[#Data],[Total]], COUNTA(TeaSales[Date])-7, 0, 7, 1))

C. AGGREGATE – Like SUBTOTAL but more options + can ignore errors/hidden rows

Syntax: =AGGREGATE(function_num, options, ref1, [ref2], …)

function_num: 1=AVERAGE, 9=SUM, 14=LARGE, etc. options: 6=ignore hidden rows & errors, 7=ignore hidden & nested SUBTOTAL, etc.

Example – Average Total ignoring errors & hidden rows =AGGREGATE(1,6,TeaSales[Total])

4. Advanced Text Functions – TEXTBEFORE, TEXTAFTER (Excel 365 / 2022+)

These are game-changers for text parsing — much cleaner than LEFT/MID/RIGHT + FIND.

TEXTBEFORE – Everything before a delimiter TEXTAFTER – Everything after a delimiter

Example – CustomerID = “MUM-2026-0456-C123”

• City code: =TEXTBEFORE(A2,”-“) → “MUM”
• Year: =TEXTBEFORE(TEXTAFTER(A2,”-“),”-“) → “2026”
• Sequence: =TEXTAFTER(A2,”-“,2) → “0456-C123”
• Last part: =TEXTAFTER(A2,”-“, -1) → “C123” (negative = from right)

With instance_num (get 2nd occurrence): =TEXTBEFORE(A2,”-“,2) → everything before second “-” → “MUM-2026”

TEXTSPLIT (bonus – splits into array) =TEXTSPLIT(A2,”-“) → spills: MUM | 2026 | 0456 | C123

Quick Summary Table – Chapter 12

Homework / Practice (Do This Today!)

1. In a blank area → =UNIQUE(TeaSales[City]) → sorted unique cities
2. Next to it → =SORT(UNIQUE(TeaSales[Item]))
3. Create dynamic top sellers: =SORT(FILTER(TeaSales[[Item]:[Total]], TeaSales[Total]>0), 5, -1) (top 10?)
4. Use SUMIFS: total Chai sales in Mumbai last 30 days
5. Parse a column of “Order-2026-0456-MUM” with TEXTBEFORE/TEXTAFTER
6. Use AGGREGATE to average Total ignoring any #N/A errors
7. Try SEQUENCE(15) + TEXT(…, “dd-mmm”) for date list