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&””))

Download Your Example Excel File

  • 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.
Count unique text of list using Excel formula
Count unique text of list using Excel formula

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?

  • To divide Excel consider TRUE and FALSE as 1 and 0 respectively.