While working with huge datasets, we often need to **count cells in Excel** with **different text**. In this article, I will show you **5** easy and effective methods to **count cells** in **Excel** with **different text**.

**Table of Contents**hide

## Download Practice Workbook

This is the dataset that I am going to use in this article. Here, we have some **Sales Person** and their **Sales** amount. We will try to count the **different or unique texts** that are present in the **Sales** **Person column**.

## 5 Methods to Count Cells in Excel with Different Text

### 1. Applying SUM & COUNTIF to Count Cells in Excel with Different Text

Here I am going to use **the SUM function** and **the COUNTIF function** to **count cells** with **different text**.

**STEPS:**

➤ In cell **E4**, type the following formula.

`=SUM(1/COUNTIF(B4:B13,B4:B13))`

**Formula Breakdown**

➦ **COUNTIF(B4:B13,B4:B13)** —> It will get you how many times each individual value appears in the specified range.

Output : **{2;2;1;2;2;2;2;2;2;1}**

➦ **1/COUNTIF(B4:B13,B4:B13)** —> It reciprocates the output of previous steps.

Output : **{0.5;0.5;1;0.5;0.5;0.5;0.5;0.5;0.5;1}**

➦ **SUM(1/COUNTIF(B4:B13,B4:B13))** —> Sums the values **0.5**,**0.5**,**1**,**0.5**,**0.5**,**0.5**,**0.5**,**0.5**,**0.5**,**1**.

Output: {**6}**

** **➤ Now press **CTRL + SHIFT + ENTER **If you are not using **Microsoft Office 365**. You will get the number of **different text** in the range **B4:B13**.

**Note: **Please remember that you have to press **CTRL + SHIFT + ENTER *** as it is an array formula*. Notice the

**curly bracket**

**‘{}’**in the formula bar that denotes an

**array formula**.

**Read More:** **COUNTIF Cell That Contains a Specific Text in Excel (Case-Sensitive and Insensitive)**

### 2. Use of Pivot Table to Count Cells in Excel with Different Text

Now, I will use **Pivot Table** to **count cells** in **Excel** with **different text**.

** ****STEPS:**

➤ First, we have to create a **Pivot Table** with the dataset. To do so, select the range **B3:C13** >> go to **Insert** Tab >> select **Pivot Table** >> select **From Table/Range**.

➤ **PivotTable from table or range** window will appear. Now, tick the **Add this data to the Data Model** box. Then press **OK**.

Excel will create a **Pivot Table** for you.

➤ Put **Sales Person** in the **Rows Field** and **Values Field**. **Excel** will by default show you the **count of Sales Person** on the left side.

➤ Then, click the **drop-down list** of** Count of Sales Person **from the **Value Field** and select **Value Field Settings**.

➤ **Value Field Settings** dialog box will pop up. Choose **Distinct Count** as the * type of calculation*. Then click

**OK**.

➤ **Excel** will show you the total number of cells that have ** different texts**.

**Read More:** **How to Count Filtered Cells with Text in Excel (3 Methods)**

### 3. Using a User Defined Functions to Count Different Text Ignoring Blank Cells

In this section, you will see that we can use **VBA** to **count cells** in **Excel** with **different text** * ignoring the blank ones*. Please notice that

**cells**

**B6**and

**B9**are blank.

**STEPS:**

➤ Go to **Developer** tab >> select **Visual Basic**.

➤ Then go to **Insert** tab >> select **Module**.

**Module** window will show up. After that, write down the following code

```
Function COUNTDIFFERENT(DataRange As Range, CountBlanks As Boolean) As Integer
Dim CellContent As Variant
Dim DifferentValues As New Collection
Application.Volatile
On Error Resume Next
For Each CellContent In DataRange
If CountBlanks = True Or IsEmpty(CellContent) = False Then
DifferentValues.Add CellContent, CStr(CellContent)
End If
Next
COUNTDIFFERENT = DifferentValues.Count
End Function
```

Here, I have created a new **Function** **COUNTDIFFERENT**. The function has two **arguments**, **DataRange** and **CountBlanks**. They are set as **Range** and **Boolean**. The outcome or result of the function **COUNTDIFFERENT** will be in **Integer **form.

Then, I have declared two **variables **using** Dim Statement** these are **CellContent**, and **DifferentValues** as **Variant** and **New Collection** respectively.

Next, I used a **For** loop to check if there have any **Blank cells **or not in the selected cell range.

Here, I also used the **IF **statement to count or not count **Blank Cells **as **Different Text**. If the **Boolean** is **TRUE**, **Excel** will show the number of different text **including the blank cells **whereas, it will

**in case the**

__ignore the blank cells__**Boolean**is

**FALSE**.

➤ Save the program. The function is ready to use.

➤ Now, go back to your existing

**Workbook**. Then select

**cell**

**E4**and write down the following formula

`=COUNTDIFFERENT(B4:B13,FALSE)`

Here, I have selected the **range** **B4:B13** and selected the **Boolean** **FALSE** as I want to * ignore the blank cells*.

➤ Then, press

**ENTER**.

**Excel**will return you the number of cells with different text ignoring the blank ones.

### 4. Using Combined Functions to Count Distinct Texts

In this section, I will show you how to count **distinct** **texts** using the **SUM**, **ISTEXT**, and **COUNTIF** function. That means you will learn how to * count texts that are present only once in the dataset*.

**STEPS:**

In cell **E4**, type the following formula.

`=SUM(IF(ISTEXT(B4:B13)*COUNTIF(B4:B13,B4:B13)=1,1,0))`

**Formula Breakdown**

➦ **COUNTIF(B4:B13,B4:B13)** —> It determines how many times the values in the selected range appear respectively.

Output : **{2;2;1;2;2;2;2;2;2;1}**

➦ **ISTEXT(B4:B13)** —> Declares whether * the values in the selected range are Text or not*.

Output :

**{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}**

➦ **ISTEXT(B4:B13)*COUNTIF(B4:B13,B4:B13)** —> This is the logical test. **Boolean TRUE** represents **1**, and **FALSE** represents **0**.

Output: **{2;2;1;2;2;2;2;2;2;1}**

➦ **IF(ISTEXT(B4:B13)*COUNTIF(B4:B13,B4:B13)=1,1,0) **—> This returns the result by analyzing the logical test.

➦ **IF({2;2;1;2;2;2;2;2;2;1}=1,1,0)**Output:

**{0;0;1;0;0;0;0;0;0;1}**

➦ **SUM(IF(ISTEXT(B4:B13)*COUNTIF(B4:B13,B4:B13)=1,1,0))** —> Determines the sum of the previous outputs.

➦ **SUM({0;0;1;0;0;0;0;0;0;1})**Output:

**{2}**

** **➤ Now press **CTRL + SHIFT + ENTER**. **Excel** will return the result.

Notice that the result is **2** now. That’s because only **Ross** and **Carry** are the **distinct texts**. That means * they are present only once in the range*.

**Read More:** **Count If Cell Contains Text in Excel (5 Easy Approaches)**

### 5. Count Different Text ignoring Numeric or Date Values

If you have * different types of values* (for instance, Date or Number) in a column and just want to count the

**Different Text**, you can use the

**ISTEXT**and

**IF**functions along with

**the SUM**and

**COUNTIF**functions.

Please notice that I have put two numbers intentionally in

**cells**

**B7**and

**B11**.

**STEPS:**

➤ In cell **E4**, type the following formula.

`=SUM(IF(ISTEXT(B4:B13),1/COUNTIF(B4:B13, B4:B13),""))`

**Formula Breakdown**

➦ **COUNTIF(B4:B13,B4:B13)** —> It determines how many times the values in the selected range appear respectively.

Output : **{2;2;1;1;2;2;1;1;1;1}**

➦ **1/COUNTIF(B4:B13,B4:B13)** —> Determines the reciprocals of **2,2,1,1,2,2,1,1,1,1**Output :

**{0.5;0.5;1;1;0.5;0.5;1;1;1;1}**

➦ **ISTEXT(B4:B13)** —> Declares whether * the values in the selected range are Text or not*.

Output :

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

➦ **IF(ISTEXT(B4:B13),1/COUNTIF(B4:B13, B4:B13),””)** —> Returns the output analyzing the logical test.

Output : **{0.5;0.5;1;””;0.5;0.5;1;””;1;1}**

➦ **SUM(IF(ISTEXT(B4:B13),1/COUNTIF(B4:B13, B4:B13),””))** —> Determines the sum of the previous outputs.

Output : {**6}**

➤ Now press **CTRL + SHIFT + ENTER**. **Excel** will return you the number of **different text** * ignoring other types of values*.

## Practice Workbook

Practice makes you perfect. So it is always better to practice the methods to **count cells** in **Excel** with **different text** to internalize them. That’s why I have attached a practice sheet for you.

## Conclusion

In this article, I have shown you **5** methods to count cells in **Excel **with **different texts**. I hope you will find this helpful. Lastly, if you have any comments, please leave them in the comment box.