Lessons › Excel & Google Sheets
Excel & Google Sheets lesson
UNIQUE and counting distinct
Nine orders, but only three different locations: Lagos, Abuja and Accra. It's SQL's COUNT(DISTINCT location).
Try this lesson free →Example
COUNTA counts every order. UNIQUE keeps each location once, so COUNTA(UNIQUE(...)) counts the different locations:
=COUNTA(UNIQUE(C2:C10))| Order | Day | Location | Product | Qty | Revenue | Report | Answer | |
|---|---|---|---|---|---|---|---|---|
| O101 | Mon | Lagos | Earbuds | 2 | 70 | Orders | ||
| O102 | Mon | Abuja | Charger | 1 | 15 | Locations | ||
| O103 | Tue | Lagos | Phone case | 3 | 24 | Products | ||
| O104 | Wed | Lagos | Earbuds | 1 | 35 | Days with orders | ||
| O105 | Wed | Abuja | Earbuds | 4 | 140 | |||
| O106 | Thu | Lagos | Charger | 2 | 30 | |||
| O107 | Fri | Abuja | Phone case | 5 | 40 | |||
| O108 | Fri | Lagos | Earbuds | 3 | 105 | |||
| O109 | Fri | Accra | Earbuds | 2 | 70 |
Key points
UNIQUELists each value once. UNIQUE(C2:C10) gives Lagos, Abuja, Accra.
COUNTA(UNIQUE(...))Counts the different values: 3 locations. Like COUNT(DISTINCT) in SQL.
Plain COUNTACounts every row, repeats and all: 9 orders. Use it for how many orders, not how many different.
Put together: =COUNTA(UNIQUE(C2:C10)). UNIQUE works in Google Sheets and Excel 365; older Excel doesn't have it.
Practice
1. In I3, count the different locations.
=COUNTA(UNIQUE(C2:C10))2. In I2, count the orders.
=COUNTA(A2:A10)3. In I4, count the different products.
=COUNTA(UNIQUE(D2:D10))4. In I5, count how many different days had orders.
=COUNTA(UNIQUE(B2:B10))Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.
Try this lesson free →