This allows Excel to return an array of all names. Note that the formula references the entire range of names in A2:A12 and B2:B12.
#PIVOT TABLE COUNT UNIQUE VALUES MAC EXCEL 2013 FULL#
This example uses the ampersand (&) to concatenate last name and first name into a full name. This can be useful if you want to identify people who have not returned for additional service, so you can contact them. This example has the exactly_once argument set to TRUE, and the function returns only those customers who have had service one time. This example uses SORT and UNIQUE together to return a unique list of names in ascending order.
This is an array formula, so be sure to press Ctrl + Shift + Enter to complete it.
The solution is to use the SUM function in combination with IF and COUNTIF: SUM (IF (COUNTIF ( range, range )1,1,0)) Note. This means that Excel will dynamically create the appropriate sized array range when you press ENTER. Supposing you have a column of names in your Excel worksheet, and you need to count unique names in that column. The UNIQUE function will return an array, which will spill if it's the final result of a formula. In the examples above, the arrays for our UNIQUE formulas are range D2:D11, and D2:D17 respectively. An array can be thought of as a row or column of values, or a combination of rows and columns of values.