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.

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.

**Table of Contents**hide

## How to Count Cells in Excel with Different Text: 5 Ways

### 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: **How to Count Cells with Specific Text in Excel

### 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. 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.

**Read More: **Excel VBA to Count Cells Containing Specific Text

### 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:** How to Apply COUNTIF When Cell Contains Specific Text

### 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.

**Download Practice Workbook**

## 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.