Resolving COUNTIF UNIQUE Error in Excel
If you're trying to create an Excel function that counts the number of unique entries in a range of potentially merged cells, you might encounter an issue when using the UNIQUE function. This function returns two values for merged cells: one for the merged cell and zero for the individual cells within the merge. In this article, we'll explore how to resolve this issue and provide a detailed explanation of the key concepts involved.
Key Concepts
- COUNTIF function
- UNIQUE function
- Merged cells
- Arrays
- Helper columns
Using COUNTIF and UNIQUE Functions
The COUNTIF function in Excel counts the number of cells within a range that meet a single condition. The UNIQUE function returns an array of unique values in a range. However, when used together, they may not provide the desired result when dealing with merged cells.
=SUM(COUNTIF(UNIQUE(range),range))
The above formula counts the unique values in a range, but it does not account for merged cells. The UNIQUE function will only return the merged cell value, while the individual cells within the merge will be ignored.
Resolving the COUNTIF UNIQUE Error
To resolve this issue, we can use a helper column to extract the unique values from the merged cells. This can be done using the following formula:
=IF(ISNUMBER(SEARCH(range,text_to_search)),"Unique","")
This formula searches for the value in the range within a text string and returns "Unique" if it is found. The resulting array can then be used with the COUNTIF function to count the unique values:
=SUM(COUNTIF(helper_column, "Unique"))
Example
Consider the following range of merged cells:
| A | B | C | D |
|---|---|---|---|
| 1 | Apple | Banana | Apple |
| 2 | Cherry | Banana | Cherry |
| 3 | Apple |
To count the number of unique entries, we can use the following formula:
=SUM(COUNTIF(helper_column, "Unique"))
Where the helper column is:
=IF(ISNUMBER(SEARCH(B2:D2,TEXTJOIN("",TRUE,B2:D2))),"Unique","")
References
- COUNTIF Function:
- UNIQUE Function:
- SEARCH Function:
- TEXTJOIN Function: