Task 1 of 3
In D2, list the cities without duplicates, in alphabetical order, with a single formula.
Lesson theory
In modern Excel a formula can return many values at once. They "spill" down and to the right of the formula cell.
If a filled cell is in the way, you get a #SPILL! error.
=UNIQUE(B2:B13)returns a list without duplicates;=SORT(UNIQUE(B2:B13))returns the same list in alphabetical order;D2#refers to the whole spilled range starting at D2, however many rows it has.
=COUNTIF(B2:B13,D2#) counts the visits for every city at once.Practice is part of Pro
The live spreadsheet, task checking and .xlsx files in all 170 lessons. Your progress is saved in this browser; sync across devices is coming soon.