You could use UNIQUE and FILTER functions instead of SUMPRODUCT. However, the UNIQUE function is not available in Excel before 2021. The FILTER function is available from Excel 2019. So, if your Excel version is older than 2019, SUMPRODUCT can be a suitable option for you to count unique va...
Note.Currently the UNIQUE function is only available in Excel for Microsoft 365 and Excel 2021. Excel 2019, 2016 and earlier do not support dynamic array formulas, so the UNIQUE function is not available in these versions. Basic UNIQUE formula in Excel Below is an Excel unique values formula ...
Note: The UNIQUE function is only available in Excel for Microsoft 365, Excel 2021 or newer versions of Excel, and Excel for the web. Syntax =UNIQUE(array,[by_col],[exactly_once]) Arguments array (required):The range or array to retrieve unique values. ...
Sometimes we want a unique list with two columns that are not next to each other. In this circumstance, we can use the CHOOSE or CHOOSECOLS (only available in Excel 365) functions to reorder the columns. The example below uses the CHOOSE function and is available in Excel 2021 and Excel ...
Name of ErrorsWhen Occurs #CALC! If the UNIQUE function cannot extract the unique values. #SPILL! If there is any value in the spill range from which the UNIQUE function will return the list. #VALUE! If the output (sorted unique values) is not available in the given dataset....
For instance, the rowBrazilNois present more than once in the table, so it is not listed here. If needed, you can download the Excel file used here:unique.xlsx Note: this function is only available with Office 365.
To count unique values in Excel ignoring blanks, employ the FILTER function to filter out empty cells, and then warp it in the already familiar COUNTA UNIQUE formula: COUNTA(UNIQUE(FILTER(range,range<>""))) With the source data in B2:B11, the formula takes this form: ...
If you’re not yet a Microsoft 365 subscriber, you may choose to use the UNIQUE function in Excel online. Since there is no backward compatibility for this function, it won’t work when opened with any older version of Excel. So you may have to choose a more traditional method, like pi...
The COUNTIF function is also covered in more depth in theExpert Skills course. Example workbook You can download an example workbook showing this validation in action. UniqueConstraint.xlsx These are theonlyup-to-date Excel books currently published and includes the new Dynamic Arrays features. ...
{"boardId":"excelgeneral","messageSubject":"unique-function-in-excel-for-mac","messageId":"3675169","replyId":"3675196"},"buildId":"HEhyUrv5OXNBIbfCLaOrw","runtimeConfig":{"buildInformationVisible":false,"logLevelApp":"info","logLevelMetrics":"info","openTelemetryClientEnabled":false,"...