Excel Practice
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

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.

On a spilling Excel the lists themselves appear on the sheet and can be referred to. Here, and in any Excel before 2019, only the first cell shows. That is why the wrapped forms are the ones to learn first. They give the same answer on every version.

The mistake to watch for

Reading only the first cell. On a sheet that does not spill, a bare UNIQUE, FILTER or SORT shows its first value and nothing else. That looks like a single answer, and it is the top of a list. Wrap the array in COUNTA, SUM, ROWS or INDEX, and the formula gives the same answer on every version of Excel.

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, how many different shops appear in the log.

To begin, type it exactly:

=COUNTA(UNIQUE(B2:B8))

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, how many different shops appear in the log. To begin, type it exactly: =COUNTA(UNIQUE(B2:B8)).

    Hint. COUNTA of UNIQUE.

  2. 2

    In E4, the Station shop's total takings, with FILTER.

    Hint. SUM of the FILTER.

  3. 3

    In E6, the day with the highest single takings. Sort A2:C8 by its third column, descending, and read row 1, column 1.

    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, write a formula using INDEX and SORT that gives the second-highest takings figure.

    Hint. SORT descending, INDEX 2.