Array Formulas

Array formulas are Excel's superpower. Perform calculations on multiple values at once, replacing hundreds of formulas with one.

What is an Array?

An array is a collection of values:

{1, 2, 3, 4, 5}

A range like A1:A5 is an array.

Excel 365 vs Older Excel

Excel 365:

Just press Enter
Formulas auto-spill
Modern and easier!

Excel 2019 and older:

Press Ctrl+Shift+Enter
Excel adds {=formula}

Your First Array Formula

Calculate Qty × Price and sum:

Data:

A	B
Qty	Price
5	10
3	15
2	20

4 rows

Array formula:

=SUM(A2:A4 * B2:B4)

Excel 365: Press Enter Older Excel: Ctrl+Shift+Enter

Result: 155 (50+45+60)

No helper column needed!

Array Operations

Multiplication:

={1;2;3} * {10;20;30}
Result: {10;40;90}

Addition:

={1;2;3} + {10;20;30}  
Result: {11;22;33}

Conditional Array Formulas

Count where Dept="Sales" AND Sales>50000:

=SUM((B2:B10="Sales") * (C2:C10>50000))

How it works:

Creates TRUE/FALSE arrays
Multiplies them (TRUE=1, FALSE=0)
Sums the results

Dynamic Array Functions (Excel 365)

FILTER

Get all rows where Sales > 50000:

=FILTER(A2:C10, C2:C10>50000)

UNIQUE

Get unique departments:

=UNIQUE(B2:B10)

SORT

Sort by sales descending:

=SORT(A2:C10, 3, -1)

SEQUENCE

Create numbered list 1-10:

=SEQUENCE(10)

RANDARRAY

Generate 10 random numbers:

=RANDARRAY(10)

Practical Examples

Multi-Criteria Sum

Sum sales for "Sales" dept with sales > 50K:

=SUM((B2:B10="Sales") * (C2:C10>50000) * C2:C10)

Multi-Criteria Lookup

Find price for Product="Widget A" AND Color="Red":

=INDEX(D2:D10, MATCH(1, (A2:A10="Widget A")*(B2:B10="Red"), 0))

Ctrl+Shift+Enter in older Excel.

Performance Tips

Slow:

=SUM(A:A * B:B)

Checks 1 million rows!

Fast:

=SUM(A2:A1000 * B2:B1000)

Checks only 999 rows.

Common Errors

#VALUE!: Array sizes don't match Fix: Ensure equal-sized ranges

#SPILL! (Excel 365): Not enough space Fix: Clear cells where formula spills

Slow performance: Too large range Fix: Limit range size

When to Use Array Formulas

Use when:

✅ Multiple criteria needed
✅ Avoid helper columns
✅ Complex range calculations
✅ You have Excel 365

Avoid when:

❌ Simple formula works
❌ Massive datasets
❌ Too complex to maintain

Summary

Array formulas let you:

Calculate on multiple cells at once
Eliminate helper columns
Create powerful one-formula solutions

Excel 365 Functions:

FILTER, UNIQUE, SORT
SEQUENCE, RANDARRAY
Auto-spilling

Traditional Array:

{=SUM(A1:A10 * B1:B10)}

Master these and unlock Excel's true power! 🚀