Unique values

Advanced← PreviousSkip →
Exercise 2 of 11UNIQUE

List each category once.

Write your formula in the green cell, H2.

Dynamic arrays

Filter a listUnique valuesOut of stockFILTER by a numberCount unique valuesSort by priceTop 3 with TAKEStock value with FILTERFILTER with ANDFILTER with ORName parts with LET
inventory.xlsx
fx
1SKUProductCategoryPriceStock
2P-01Desk lampLighting$39.00120Categories
3P-02Office chairFurniture$189.0014
4P-03Standing deskFurniture$449.000
5P-04Monitor armAccessories$79.0056
6P-05Floor lampLighting$89.000
7P-06BookshelfFurniture$129.0031
8P-07Cable trayAccessories$25.00210
9P-08Desk matAccessories$19.000
10
11

Click a cell to add it to your formula, drag to select a range, or click a column letter for the whole column.

About dynamic arrays in Excel

Modern Excel formulas can return a whole list that spills into the cells below. Practice FILTER, UNIQUE and SORTBY on an inventory table, and combine them with SUM for one-cell answers.

Functions you can use here

  • FILTER(array, include, [if_empty])
  • UNIQUE(array, [by_col], [exactly_once])
  • SORTBY(array, by_array1, [sort_order1], …)
  • LET(name1, value1, [name2, value2, …], calculation)

More dynamic arrays exercises

  1. 1.Filter a list
  2. 2.Unique values
  3. 3.Out of stockPremium
  4. 4.FILTER by a numberPremium
  5. 5.Count unique valuesPremium
  6. 6.Sort by pricePremium
  7. 7.Top 3 with TAKEPremium
  8. 8.Stock value with FILTERPremium
  9. 9.FILTER with ANDPremium
  10. 10.FILTER with ORPremium
  11. 11.Name parts with LETPremium
See the whole topic →