Learning Excel: Combining arrays
Combining
Will wants a small overview per zone: the zones in alphabetical order, with how many rides and how many visitors next to each. If a zone is added next year, the overview has to grow with it by itself.
One formula inside another
A list can be the argument of another function. In =SORT(UNIQUE(B2:B13)), UNIQUE first makes the list of zones, and SORT puts it in alphabetical order.
It works with FILTER too: =SUM(FILTER(D2:D13,B2:B13="Water")) adds up only the visitors of the water rides.
=SORT(UNIQUE(B2:B13))
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Ride | Zone | Queue time | Visitors | Height | Result | Zone | Water | ||||||
| 2 | Whirlwind | Water | 45 | 1,200 | 120 | Adventure | Max. wait | 20 | ||||||
| 3 | Haunted Castle | Adventure | 25 | 800 | 100 | Funfair | Child height | 105 | ||||||
| 4 | Log Flume | Water | 35 | 1,500 | 110 | Kidsland | ||||||||
| 5 | Dodgems | Funfair | 10 | 650 | 130 | Space | ||||||||
| 6 | Big Wheel | Funfair | 15 | 900 | 90 | Water | ||||||||
| 7 | Pirate Ship | Adventure | 30 | 1,100 | 110 | |||||||||
| 8 | Pedal Boats | Water | 5 | 400 | 85 | |||||||||
| 9 | Corkscrew | Adventure | 50 | 1,700 | 140 | |||||||||
| 10 | Mini Train | Kidsland | 10 | 500 | 85 | |||||||||
| 11 | Rocket | Space | 40 | 1,300 | 130 | |||||||||
| 12 | Carousel | Kidsland | 5 | 600 | 85 | |||||||||
| 13 | Teacups | Funfair | 10 | 700 | 95 | |||||||||
| 14 |
Referring to a list
With a # after a cell, you refer to the whole list that spills from that cell. G2# is the list of zones, however long it gets.
In H2, =COUNTIF(B2:B13,G2#) counts the rides of each zone, and spills too: one number per zone.
=COUNTIF(B2:B13,G2#)
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Ride | Zone | Queue time | Visitors | Height | Result | Zone | Water | ||||||
| 2 | Whirlwind | Water | 45 | 1,200 | 120 | Adventure | 3 | Max. wait | 20 | |||||
| 3 | Haunted Castle | Adventure | 25 | 800 | 100 | Funfair | 3 | Child height | 105 | |||||
| 4 | Log Flume | Water | 35 | 1,500 | 110 | Kidsland | 2 | |||||||
| 5 | Dodgems | Funfair | 10 | 650 | 130 | Space | 1 | |||||||
| 6 | Big Wheel | Funfair | 15 | 900 | 90 | Water | 3 | |||||||
| 7 | Pirate Ship | Adventure | 30 | 1,100 | 110 | |||||||||
| 8 | Pedal Boats | Water | 5 | 400 | 85 | |||||||||
| 9 | Corkscrew | Adventure | 50 | 1,700 | 140 | |||||||||
| 10 | Mini Train | Kidsland | 10 | 500 | 85 | |||||||||
| 11 | Rocket | Space | 40 | 1,300 | 130 | |||||||||
| 12 | Carousel | Kidsland | 5 | 600 | 85 | |||||||||
| 13 | Teacups | Funfair | 10 | 700 | 95 | |||||||||
| 14 |
Try it yourself
How many visitors did the zone in N1 have today? Add them up in G2 with FILTER.
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Ride | Zone | Queue time | Visitors | Height | Result | Zone | Water | ||||||
| 2 | Whirlwind | Water | 45 | 1,200 | 120 | Max. wait | 20 | |||||||
| 3 | Haunted Castle | Adventure | 25 | 800 | 100 | Child height | 105 | |||||||
| 4 | Log Flume | Water | 35 | 1,500 | 110 | |||||||||
| 5 | Dodgems | Funfair | 10 | 650 | 130 | |||||||||
| 6 | Big Wheel | Funfair | 15 | 900 | 90 | |||||||||
| 7 | Pirate Ship | Adventure | 30 | 1,100 | 110 | |||||||||
| 8 | Pedal Boats | Water | 5 | 400 | 85 | |||||||||
| 9 | Corkscrew | Adventure | 50 | 1,700 | 140 | |||||||||
| 10 | Mini Train | Kidsland | 10 | 500 | 85 | |||||||||
| 11 | Rocket | Space | 40 | 1,300 | 130 | |||||||||
| 12 | Carousel | Kidsland | 5 | 600 | 85 | |||||||||
| 13 | Teacups | Funfair | 10 | 700 | 95 | |||||||||
| 14 |
Ready for more?
The full lesson has 5 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson