# Count Unique Text of a list in Excel

After reading this article, you will be able to create a formula for counting unique text of a given list, excluding empty cells.

Let’s understand with an example, you have a list of Product Managers, but the problem is that they have duplicate entries. Read also count unique text with the condition using a formula or pivot table.

However, the assignment is to find unique texts among the list of entries by creating Excel formula.

## Formula to Implement:

=SUMPRODUCT((B4:B14<>””)/COUNTIF(B4:B14,B4:B14&””))

• While writing this I used Office 365, however, anyone using Excel 2016, Excel 2013 and even prior editions of Excel should be able to follow along with the content.

## Functions applicable:

• SUMPRODUCT – Multiply the corresponding value and sum.
• COUNTIF – Code number by converting a specific reference

Syntax

• =SUMPRODUCT(array1, [array2], [array3], …)
• =COUNTIF(range, criteria)

## Formula Explanation (Step by Step)

Excel start calculating from the innermost parenthesis and then work outward. Let’s break our formula into multiple small steps to understand it’s application.

First Formula(B4:B14<>””)

Step – 1

Result

{TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

The first part of the formula to check if any cell is empty or not.

Note: Empty cell in B7 returns FALSE

Second FormulaCOUNTIF(B4:B14,B4:B14&””))

Step – 2

Result

{2;3;1;1;3;3;2;3;3;1;3}

This formula count set of elements in a list. Like, Moss and Furn occur thrice in the list and a Blank (Cell- C7) count is once.

Step – 3

In this step Excel divide Step – 1 and Step – 2 as below.

={TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE} divide (/) {2;3;1;1;3;3;2;3;3;1;3}
={0.5;0.333;1;0;0.333;0.333;0.5;0.333;0.333;1;0.333}
= 5

By dividing the number of counts results to decimals. So that it sums each unique number as 1. Like if a text occurs 4 times after dividing it returns in 0.25.

Return value of 5 is the count of unique texts of a list, excluding blank cell.

Note: Did you notice returns of FALSE i.e. Zero?