which returns 7, since there are seven unique colors in B5:B16.
This example uses the UNIQUE function to extract unique values. When UNIQUE is provided with the range B5:B16, which contains 12 values, it returns the 7 unique values seen in D5:D11. These are returned directly to the COUNTA function as an array like this:
Unlike the COUNT function, which counts only numbers, COUNTA counts both text and numbers. Since there are seven items in array, COUNTA returns 7. This formula is dynamic and will recalculate immediately when source data is changed.
With a cell reference
You can also refer to a list of unique values already extracted to the worksheet with the UNIQUE function using a special kind of cell reference. The formula in D5 is:
The hash character (#) tells Excel to refer to the spill range created by UNIQUE. Like the all-in-one formula above, this formula is dynamic and will adapt when data is added or removed from the original range.
Count unique ignore blanks
To count unique values while ignoring blank cells, you can add the FILTER function like this:
UNIQUE won't automatically change the source range if data is added or deleted. To give UNIQUE a dynamic range that will automatically resize as needed, you can use an Excel Table, or create a dynamic named range with a formula.
The core of this formula is a basic lookup with INDEX: = INDEX ( list , row ) In other words, give INDEX the list and a row number, and INDEX will retrieve a value to add to the unique list. The hard work is figuring out the ROW number to give INDEX...
This example uses the UNIQUE function. With default settings, UNIQUE will output a list of unique values, i.e. values that appear one or more times in the source data. However, UNIQUE has an optional third argument, called "occurs_once" that, when...
This example uses the UNIQUE function together with the FILTER function. Working from the inside out, the FILTER function is first used to remove any blank values from the data: FILTER ( B5:B16 , B5:B16 <> "" ) The symbol is a...
This example uses the UNIQUE function together with the FILTER function. Working from the inside out, the FILTER function is first used to remove limit data to values associated with group A only: FILTER ( B5:B16 , C5:C16 = E4 ) Notice we are...
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.