Lessons Lesson 90

Dynamic Arrays: Putting It Together

About this lesson

The dynamic array functions produce lists. UNIQUE the distinct values, FILTER the rows that pass a test, SORT the range in order, SEQUENCE a series. Wrapped in COUNTA, SUM, ROWS or INDEX, each list becomes one number or one cell.

By the end you can

  • pair a list-making function with a summarising one.
  • answer count-distinct, sum-if and best-day questions in one cell each.

Practises UNIQUE FILTER SORT SEQUENCE INDEX ROWS

By Ali, Founder, Excel Lessons

The idea

Four functions that make lists, and four that read them. UNIQUE, FILTER, SORT and SEQUENCE each return an array. COUNTA, SUM, ROWS and INDEX each turn an array into a single answer. Every formula in this module is one from each group. That pairing is the whole skill.

A list on its own spills: the formula sits in one cell and its results fill the cells below. A list wrapped in COUNTA or SUM does not — it comes back as one number, in the one cell. Both are useful and they answer different questions. "Which customers" wants the list. "How many customers" wants the number.

The mistake to watch for

Typing under a formula that spills. The block belongs to the formula at its top, and Excel refuses to write over anything already there. So one label in the way turns the whole answer into #SPILL!. Leave the cells under a bare UNIQUE, FILTER or SORT clear. Or wrap the formula, so it only ever fills the one cell.

Read more about this

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In E2, type:

=COUNTA(UNIQUE(B2:B8))

That counts the different shops in the log.

E2
Row A B C D E F G
1 Date Shop Takings Shops
2 Mon High St 820
3 Mon Station 410 Station total
4 Tue High St 760
5 Tue Station 455 Best day
6 Wed High St 905
7 Wed Station 390 Days over 800
8 Thu High St 640
9 Second-best takings

Every step

  1. 1

    In E2, type: =COUNTA(UNIQUE(B2:B8)). That counts the different shops in the log.

    Hint. COUNTA of UNIQUE.

  2. 2

    In E4, find the Station total with FILTER and SUM.

    Hint. SUM of the FILTER.

  3. 3

    In E6, find the day with the highest takings using INDEX and SORT. Shape: =INDEX(SORT(table, col, n), 1, 1). Sort by takings, descending.

    Hint. SORT by column 3, -1.

  4. 4

    Read =ROWS(FILTER(C2:C8,C2:C8>800)) and say what it returns.

    Hint. Which figures are over 800?

  5. 5

    In E10, find the second-highest takings with INDEX and SORT.

    Hint. Sort it descending, then read position 2.