*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 Formula – COUNTIF(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.